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.....
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.