|
View previous topic :: View next topic
|
| Author |
Message |
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
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 |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
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 |
|
|
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
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 |
|
|
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
thnx again 
wat are these A and B in this case  |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi Paul,
| Quote: |
| wat are these A and B in this case |
What do u mean by this? |
|
| Back to top |
|
|
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
| 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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi Paul,
These are alias |
|
| Back to top |
|
|
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
| 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" |
|
| Back to top |
|
|
deepak.vl
New User
Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| 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 |
|
|
deepak.vl
New User
Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad
|
|
|
|
| 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 |
|
|
paul_007
New User
Joined: 03 Aug 2006
Posts: 33
Location: earth
|
|
|
|
| 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  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
|
|
