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
 
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
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

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