db2 query

[thiyagucse]

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

[Rupesh.KOthari]

HI,

[Kalicharan]

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]

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

[Krishna]

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.
_________________
Regards,
Krishna
http://www.mainframegurukul.com
http://www.mainframetutorials.com
http://www.ibmmainframeguru.com