hai all
I am new to this group.How do i select a third max marks from table without using the subquery
db2 query
Moderators: Kalicharan, Moderator Group
- Rupesh.KOthari
- Member
- Posts: 17
- Joined: Thu Feb 16, 2006 5:27 pm
HI,
Is it possibel to select 3rd max without using Subquery??
Regards
Rupesh
Firstly Welcome to forum.....hai all
I am new to this group.How do i select a third max marks from table without using the subquery
Is it possibel to select 3rd max without using Subquery??
Regards
Rupesh
------------------------
Thanks & Regards
Rupesh
-----------------------
Sun Shines Everywhere
Thanks & Regards
Rupesh
-----------------------
Sun Shines Everywhere
- Kalicharan
- Moderator
- Posts: 31
- Joined: Wed Feb 08, 2006 1:51 pm
selecting 3rd max marks from table without using subquery
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...
********************
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...
-
- Member
- Posts: 1
- Joined: Thu Aug 28, 2008 2:45 pm
- Location: PUNE
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
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
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.
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
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page