# Find max column value without using MAX function

Author Message
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

 Posted: Tue Feb 27, 2007 11:41 am can anyone pls tell me how to find max value in a column "without using MAX function" for eg to find max salary in salary column
guptae

Moderator

Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

Posted: Tue Feb 27, 2007 11:47 am

HI There,

To find the Nth MAXIMUM SALARY of the table:

 Code: SELECT SALARY FROM EMPTABLE A WHERE (N-1) IN (SELECT COUNT(*) FROM EMPTABLE B WHERE A.SALARY < B. SALARY)
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

 Posted: Tue Feb 27, 2007 11:54 am hey, thnx 4 help this means that to find first max. salary N=1 so wat we have to put in place of (N-1)
guptae

Moderator

Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

Posted: Tue Feb 27, 2007 12:02 pm

Hi There,
It should be

 Code: SELECT SALARY FROM TABLE1 A        WHERE 0 IN ( SELECT COUNT(*) FROM TABLE1 B WHERE A.SALARY < B.SALARY);
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

 Posted: Tue Feb 27, 2007 12:06 pm thnx again wat are these A and B in this case
guptae

Moderator

Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

Posted: Tue Feb 27, 2007 12:08 pm

Hi Paul,

 Quote: wat are these A and B in this case

What do u mean by this?
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

Posted: Tue Feb 27, 2007 12:12 pm

 Quote: Hi Paul, Quote: wat are these A and B in this case What do u mean by this?

in ur query u have written TABLE1 A and TABLE1 B , i am talking about this A and B
guptae

Moderator

Joined: 14 Oct 2005
Posts: 1207
Location: Bangalore,India

 Posted: Tue Feb 27, 2007 12:22 pm Hi Paul, These are alias
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

Posted: Tue Feb 27, 2007 12:26 pm

 Quote: Hi Paul, These are alias

ohhh i got confused...... i'm little weak in sql queries

and thnx a lot..... the query is working

"YOU ARE A GENIUS"
deepak.vl

New User

Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

 Posted: Sat Mar 03, 2007 12:00 pm Hi All, But this will not work in the below example. Consider the below table. I want the 4th max salary (which is 8500) Emp Salary 001 10000 002 11000 003 9000 004 8500 005 8500 006 8250 007 9000 but there are 4 records that are greater than 8500, where as our query will check for N-1 which is 3. So for getting COUNT(*), we need to use COUNT (DISTINCT SALARY). Let me know if Iam wrong.
priyesh.agrawal

Senior Member

Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

Posted: Sun Mar 04, 2007 12:00 pm

 deepak.vl wrote: but there are 4 records that are greater than 8500, where as our query will check for N-1 which is 3. So for getting COUNT(*), we need to use COUNT (DISTINCT SALARY). Let me know if Iam wrong.

That does make sense ... and would be same in case for 6th Max... Isnt it ?
deepak.vl

New User

Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

Posted: Sun Mar 04, 2007 1:17 pm

priyesh.agrawal wrote:
 deepak.vl wrote: but there are 4 records that are greater than 8500, where as our query will check for N-1 which is 3. So for getting COUNT(*), we need to use COUNT (DISTINCT SALARY). Let me know if Iam wrong.

That does make sense ... and would be same in case for 6th Max... Isnt it ?

True
paul_007

New User

Joined: 03 Aug 2006
Posts: 33
Location: earth

Posted: Mon Mar 05, 2007 8:02 pm

 Quote: priyesh.agrawal wrote: deepak.vl wrote: but there are 4 records that are greater than 8500, where as our query will check for N-1 which is 3. So for getting COUNT(*), we need to use COUNT (DISTINCT SALARY). Let me know if Iam wrong. That does make sense ... and would be same in case for 6th Max... Isnt it ? True

guys u r correct

pls can sumone help regarding this
dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

 Posted: Mon Mar 05, 2007 10:15 pm Hello, What business reason is there to not use MAX? I suppose you could declare a cursor, select the data ordered by descending salary, and fetch only the first row, and you would have the highest salary. If you want the nth highest salary, fetch that many rows. If you want to exclude duplicates, use "distinct".
vini_srcna

Active User

Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

 Posted: Tue Mar 06, 2007 3:18 pm wat are these A and B in this case Hi Paul, These are alias In DB2 Alias means something else. I would say its just a short name. TABLE1 A, TABLE2 B --> Here A & B are not called as alias if am right. Its just a short name assigned to the tables which are valid only for that query. Instead of addressing as TABLE1.colname it could be asssigned as A.colname and so on. Alias referes to a DB2 object which is like alternate name to the table. Correct me if am wrong..!! Thanks
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Find the occurrence of Key Field (Par... DFSORT/ICETOOL 6 Split a record with data in a differe... DFSORT/ICETOOL 8 Find a record count/numeric is multip... COBOL Programming 1 Need to find a specific STRING COBOL Programming 11 Infosphere Optim - unable to save Col... IBM Tools 0
Search our Forums:

 IBMMainframes.com is not an official and/or affiliated with IBM® in anyway Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us