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 How to force a select query to abend neo4u DB2 2 Mon Apr 23, 2018 1:36 pm
No new posts SELECT TO MULIPLE OUTFIL speermb DFSORT/ICETOOL 5 Fri Mar 23, 2018 10:44 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm
No new posts Need Guidance regarding UPDATE query Poha Eater DB2 3 Mon Feb 12, 2018 11:35 pm

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