Posted by Hugo Kornelis on 06/11/06 20:30

On 10 Jun 2006 07:48:38 -0700, aamircheema@gmail.com wrote:

(snip)
>I tried
>
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: if-then in select stmnt
• Prev in forum: Re: if-then in select stmnt
• Thread view: Re: A basic question: Removing duplicate results from Max function

[Reply to this message]