Scalar function say MAX in where clause of a SELECT querY

mfguy · New User Joined: 31 Jul 2003 Posts: 4

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

tsurya · New User Joined: 01 Aug 2003 Posts: 1 Location: Chennai

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.

mdtendulkar · Active User Joined: 29 Jul 2003 Posts: 237 Location: USA

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

vinoth · New User Joined: 06 Aug 2003 Posts: 1 Location: chennai

i want full sql queries

please guide me us.

waiting for ur reply

regards
vinoth

mdtendulkar · Active User Joined: 29 Jul 2003 Posts: 237 Location: USA

Hello,

Please tell your requirement in detail.

Regards

Mayuresh