IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query to select second max in a series


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 14, 2008 12:08 pm
Reply with quote

hi all,

Query to select second max in a series
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Nov 14, 2008 12:43 pm
Reply with quote

Code:

select max(col_name) from table_name
where col_name not in (select max(col_name) from table_name)
Back to top
View user's profile Send private message
sant532

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 14, 2008 1:04 pm
Reply with quote

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

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Nov 14, 2008 1:15 pm
Reply with quote

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

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 14, 2008 1:36 pm
Reply with quote

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

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Nov 14, 2008 2:54 pm
Reply with quote

State your original requirement with example.
What you have wrote is still very confusing
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Fri Nov 14, 2008 3:04 pm
Reply with quote

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

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 14, 2008 3:32 pm
Reply with quote

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

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Fri Nov 14, 2008 3:36 pm
Reply with quote

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

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 14, 2008 7:12 pm
Reply with quote

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
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Nov 14, 2008 7:45 pm
Reply with quote

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

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Nov 14, 2008 8:25 pm
Reply with quote

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

New User


Joined: 02 Jun 2008
Posts: 48
Location: India

PostPosted: Fri Nov 14, 2008 8:37 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Fri Nov 14, 2008 8:42 pm
Reply with quote

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

New User


Joined: 28 Dec 2006
Posts: 6
Location: Delhi

PostPosted: Mon Nov 24, 2008 4:04 pm
Reply with quote

Select Max(salary) from EmpTable
where salary<max(salary) from EmpTable
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts Inserting into table while open selec... DB2 1
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top