View previous topic :: View next topic
|
Author |
Message |
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
hi all,
Query to select second max in a series |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Code: |
select max(col_name) from table_name
where col_name not in (select max(col_name) from table_name)
|
|
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
Thanks Sambhaji!
Can wu write the same query for some (5 numbers like 1,2,3,4,5) hard coded values instead of column name? |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Quote: |
Can wu write the same query for some (5 numbers like 1,2,3,4,5) hard coded values instead of column name?
|
Elaborate more. Am |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
i have values 1,2,3,4,5,6
we need to use these values in the query and need to select 2nd highest value of the series. |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
State your original requirement with example.
What you have wrote is still very confusing |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Sambhaji,
I think he has some numbers and he wants the second highest number from the set.
sant532,
Are these numbers stored in a DB2 table? If not, Why would you want to do this using sql? |
|
Back to top |
|
|
mallik4u
New User
Joined: 17 Sep 2008 Posts: 75 Location: bangalore
|
|
|
|
Hi,
First:
-----
If your requirement is to find Nth MAX (2nd MAX as per the first stated problem), you can use the following SQL as a model to solve your problem. Here i took EMP table as an example.
Select * from Emp E1
where 2 = (Select count(distinct(E2.Salary)) From Emp E2
Where E2.salary > E1.Salary)
Second:
---------
Finding the Nth MAX in the given numbers.
As other friends asked, your requirement is not clear. where are you storing the numbers.
I am assuming that you just need to find out MAX number in the list. Nothing to do with Columns/tables. If that is the case then you can use following query.
SELECT MAX(1,2,3,4,5) FROM SYSIBM.SYSDUMMY1;
Hope you probs solved :) |
|
Back to top |
|
|
mallik4u
New User
Joined: 17 Sep 2008 Posts: 75 Location: bangalore
|
|
|
|
Sorry forgot to mention the note in my previous mail.
Number mentioned after WHERE in the first query should be less than one number of which MAX record you want to fine. For example to find 3rd MAX i have to mention 2.
Hope you understand it. |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
hi all,
I have tried with following two queries but i'm not getting the correct result
The series is 1,2,3,4,5 (no table,directly written ini query)
I want 2nd highest value should be selected i.e, 4
SELECT MAX(1,2,3,4,5) AS SAN
FROM SYSIBM.SYSDUMMY1
WHERE SAN NOT IN
(SELECT MAX(1,2,3,4,5)
FROM SYSIBM.SYSDUMMY1);
SELECT MAX(1,2,3,4,5)
FROM SYSIBM.SYSDUMMY1
WHERE MAX(1,2,3,4,5) NOT IN
(SELECT MAX(1,2,3,4,5)
FROM SYSIBM.SYSDUMMY1);
Can any one gimme solution for this!
Thanks all. |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Both of your queries are simliar and wont work. Because the subquery in the where condition evaluates to false. i.e its more like specifying 1=2 (or rather 5<>5 in this case) in the where clause. |
|
Back to top |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008 Posts: 148 Location: Chennai, India
|
|
|
|
I think you can try this.
WITH TEMP(X) AS (
SELECT X FROM TABL1
ORDER BY X DESC FETCH FIRST 2 ROWS ONLY)
SELECT T2.X FROM TABL2 T2
WHERE X = (SELECT MIN(X) FROM TEMP); |
|
Back to top |
|
|
sant532
New User
Joined: 02 Jun 2008 Posts: 48 Location: India
|
|
|
|
Hi all,
We can get the result using a column name of any table but i want the result using hard coded values.
NOTE:
select max(col_name) from table_name
where col_name not in (select max(col_name) from table_name)
The above query works fine for selecting second highest value BUT I don't want use a column name.I want result for hard coded values.
Thnaks all. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
meditate on the meaning words before posting ..
if the values are hardcoded why waste resources and people's time
You do not need a query nor any algorithm to find out |
|
Back to top |
|
|
dronveer
New User
Joined: 28 Dec 2006 Posts: 6 Location: Delhi
|
|
|
|
Select Max(salary) from EmpTable
where salary<max(salary) from EmpTable |
|
Back to top |
|
|
|