Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2285
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: 10308
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
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us