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
 

 

Scalar function say MAX in where clause of a SELECT querY

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mfguy

New User


Joined: 31 Jul 2003
Posts: 4

PostPosted: Thu Jul 31, 2003 11:19 am    Post subject: Scalar function say MAX in where clause of a SELECT querY
Reply with quote

HI
could anypone please guide on this.
Can we use the scalar function say MAX in the where clause of a SELECT query to see a user with maximum values ?(or) we have to go for subquery with the inner query selecting the max value
i.e
select user from table
where value in
( select max(value) from table
where user='xxx')
Just have a doubt on the first part of the question.Help appreciated
Thanks
mfguy
Back to top
View user's profile Send private message

tsurya

New User


Joined: 01 Aug 2003
Posts: 1
Location: Chennai

PostPosted: Fri Aug 01, 2003 12:06 pm    Post subject:
Reply with quote

Hi
U can use scalar function MAX in Where clause.

select EMP-NAME from EMP-table
where SALARY=
( select max(SALARY) from EMP-table
where DEPT-CD='xxx') ;

Thanks,
Surya.
Back to top
View user's profile Send private message
mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Fri Aug 01, 2003 6:42 pm    Post subject:
Reply with quote

Hello,

Similarly you can use this subquery to retrieve the nth max or min value for the column also.


Select A.sal from emp A
where 2 = (select count (distinct B.sal) from emp B where B.sal >= A.sal)

Here, 2nd max salary will be retrieved.

Similar results will be obtained from following query

select max(salary) from employee where
salary < (select max(salary) from employee)


Hope this will help you.

Regards

Mayuresh
Back to top
View user's profile Send private message
vinoth

New User


Joined: 06 Aug 2003
Posts: 1
Location: chennai

PostPosted: Wed Aug 06, 2003 4:02 pm    Post subject:
Reply with quote

i want full sql queries

please guide me us.

waiting for ur reply

regards
vinoth
Back to top
View user's profile Send private message
mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Thu Sep 11, 2003 9:09 am    Post subject:
Reply with quote

Hello,

Please tell your requirement in detail.


Regards

Mayuresh
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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