Query to select second max in a series

sant532 · New User Joined: 02 Jun 2008 Posts: 48 Location: India

hi all,

Query to select second max in a series

Escapa · Posted: Fri Nov 14, 2008 12:43 pm

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?

Escapa · Posted: Fri Nov 14, 2008 1:15 pm

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.

Escapa · Posted: Fri Nov 14, 2008 2:54 pm

State your original requirement with example.
What you have wrote is still very confusing

Arun Raj · Posted: Fri Nov 14, 2008 3:04 pm

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?

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 :)

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.

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.

Bharath Bhat · Posted: Fri Nov 14, 2008 7:45 pm

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.

murugan_mf · Posted: Fri Nov 14, 2008 8:25 pm

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);

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.

enrico-sorichetti · Posted: Fri Nov 14, 2008 8:42 pm

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

dronveer · New User Joined: 28 Dec 2006 Posts: 6 Location: Delhi

Select Max(salary) from EmpTable
where salary<max(salary) from EmpTable