db2 query

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
thiyagucse
Member
Posts: 1
Joined: Sun Feb 19, 2006 10:31 pm

db2 query

Post by thiyagucse » Sun Feb 19, 2006 10:35 pm

hai all
I am new to this group.How do i select a third max marks from table without using the subquery

User avatar
Rupesh.KOthari
Member
Posts: 17
Joined: Thu Feb 16, 2006 5:27 pm

Post by Rupesh.KOthari » Mon Feb 20, 2006 11:37 am

HI,
hai all
I am new to this group.How do i select a third max marks from table without using the subquery
Firstly Welcome to forum..... :D

Is it possibel to select 3rd max without using Subquery??

Regards
Rupesh
------------------------
Thanks & Regards
Rupesh
-----------------------
Sun Shines Everywhere

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

selecting 3rd max marks from table without using subquery

Post by Kalicharan » Tue Feb 21, 2006 9:18 am

Without Using SubQuery
********************

An equivalent query can also be code with a GROUP BY clause, but you
need to use a COUNT(DISTINCT x) operator to handle duplicates.
Here is the code

SELECT T1.MARK
FROM MARKSTABLE AS T1, MARKSTABLE AS T2
WHERE T1.MARK <= T2.MARK
GROUP BY T1.MARK
HAVING COUNT(DISTINCT T2.MARK) = 3

Using SubQuery
*************

SELECT T1.MARK
FROM MARKSTABLE AS T1
WHERE 3 = (SELECT COUNT(DISTINCT T2.MARK)
FROM MARKSTABLE AS T2
WHERE T1.MARK <= T2.MARK);

The performance of these two queries will vary with each SQL
implementation and with the available indexing.

Both the queries loops for n*n times where n is the total number of rows in the table.

Using TOP clause (works on SQL Server), is the effecient way of getting the nth max...

sachin.kharade
Member
Posts: 1
Joined: Thu Aug 28, 2008 2:45 pm
Location: PUNE

Post by sachin.kharade » Fri Aug 29, 2008 10:11 am

Hi,

can anyone tell me How I can decide whether to take comp or comp-3 fro a particular variable when writing program from scratch?

Regards
Sachin
kharade27@gmail.com

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

Post by Krishna » Fri Aug 29, 2008 10:31 am

Sachin,

You need to start a new topic for this in the COBOL forum.. not here.

answer to your question -

COMP-3 - we can use if variable has the decimal positions.
For s9(1) and S9(5).. programmers suggest to use COMP-3 than COMP.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others