Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Find max column value without using MAX function

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Tue Feb 27, 2007 11:41 am    Post subject: Find max column value without using MAX function
Reply with quote

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
Back to top
View user's profile Send private message

guptae

Moderator


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

PostPosted: Tue Feb 27, 2007 11:47 am    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Tue Feb 27, 2007 11:54 am    Post subject: Re: sql query for maximum value
Reply with quote

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)
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Tue Feb 27, 2007 12:02 pm    Post subject:
Reply with quote

Hi There,
It should be

Code:
SELECT SALARY FROM TABLE1 A       
WHERE 0 IN ( SELECT COUNT(*) FROM TABLE1 B
WHERE A.SALARY < B.SALARY);     
Back to top
View user's profile Send private message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Tue Feb 27, 2007 12:06 pm    Post subject: Re: sql query for maximum value
Reply with quote

thnx again icon_biggrin.gif

wat are these A and B in this case icon_rolleyes.gif
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Tue Feb 27, 2007 12:08 pm    Post subject:
Reply with quote

Hi Paul,

Quote:
wat are these A and B in this case


What do u mean by this?
Back to top
View user's profile Send private message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Tue Feb 27, 2007 12:12 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


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

PostPosted: Tue Feb 27, 2007 12:22 pm    Post subject:
Reply with quote

Hi Paul,

These are alias
Back to top
View user's profile Send private message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Tue Feb 27, 2007 12:26 pm    Post subject:
Reply with quote

Quote:
Hi Paul,

These are alias


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

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

"YOU ARE A GENIUS"
Back to top
View user's profile Send private message
deepak.vl

New User


Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

PostPosted: Sat Mar 03, 2007 12:00 pm    Post subject: Re: Find max value in a column "without using MAX funct
Reply with quote

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.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Sun Mar 04, 2007 12:00 pm    Post subject: Re: Find max value in a column "without using MAX funct
Reply with quote

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 ?
Back to top
View user's profile Send private message
deepak.vl

New User


Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

PostPosted: Sun Mar 04, 2007 1:17 pm    Post subject: Re: Find max value in a column "without using MAX funct
Reply with 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
Back to top
View user's profile Send private message
paul_007

New User


Joined: 03 Aug 2006
Posts: 33
Location: earth

PostPosted: Mon Mar 05, 2007 8:02 pm    Post subject:
Reply with quote

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 icon_confused.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Mon Mar 05, 2007 10:15 pm    Post subject:
Reply with quote

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".
Back to top
View user's profile Send private message
vini_srcna

Active User


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

PostPosted: Tue Mar 06, 2007 3:18 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Command to find TMEMBER name for a TPIPE Siva NKK Kothamasu IMS DB/DC 0 Wed Aug 16, 2017 6:08 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us