Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query to select second max in a series

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Query to select second max in a series
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 2161
Location: @my desk

PostPosted: Fri Nov 14, 2008 3:04 pm    Post subject:
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    Post subject: Reply to: Query to select second max in a series
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Nov 14, 2008 8:42 pm    Post subject: Reply to: Query to select second max in a series
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    Post subject: Hi
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    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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us