Page 1 of 1

db2 query

Posted: Sun Feb 19, 2006 10:35 pm
by thiyagucse
hai all
I am new to this group.How do i select a third max marks from table without using the subquery

Posted: Mon Feb 20, 2006 11:37 am
by Rupesh.KOthari
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

selecting 3rd max marks from table without using subquery

Posted: Tue Feb 21, 2006 9:18 am
by 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...

Posted: Fri Aug 29, 2008 10:11 am
by 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

Posted: Fri Aug 29, 2008 10:31 am
by 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.