June 7, 2007 at 5:03 am
SELECT DISTINCT (a.empsal) FROM EMP A WHERE 1 = (SELECT COUNT (DISTINCT
(b.empsal)) FROM EMP B WHERE a.empsal<=b.empsal);
June 7, 2007 at 5:47 am
This is to find the first maximum salary...this is called corelated subquery..
for each avlue of outer subquery the inner subquery will be evaluated
June 8, 2007 at 8:23 am
Thanx for the support
June 8, 2007 at 11:07 am
It must be something from a tutorial that is illustrating subqueries (and, possibly, DISTINCT). I can't see that it does anything more than
select max(empsal) from emp;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 8, 2007 at 1:11 pm
Nope tomm..The query can do much more than just getting max..
By replacing 1 with 2 in that query will fetch second maximum,
By replacing 1 with 3 in that query will fetch Thirdmaximum,
..
..
etc..
Its fairly difficult to get second, third maximums just by using MAX..
June 11, 2007 at 10:18 am
Sure, but by replacing X with Y in any number of queries, we can have lots of fun. All queries have the potential to act differently if we modify them.
Maybe, as I said, this was part of a tutorial and subsequent lessons replaced the 1 with 2, 3, etc.--or by a parameter--but as written (and the 1 was hard coded--at least in the post) the query only did one thing.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply