A basic question: Removing duplicate results from Max function

[aamircheema@gmail.com]

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like


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


The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.da te),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?

Regards,
Aamir

[Erland Sommarskog]

Re: A basic question: Removing duplicate results from Max function

(aamircheema@gm ail.com) writes:[color=blue]
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
>
> SELECT X.name,X.date,X .salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
>
> SELECT X.name,Min(X.da te),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.[/color]

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David Portas]

Re: A basic question: Removing duplicate results from Max function

aamircheema@gma il.com wrote:[color=blue]
> Hi,
>
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
>
> SELECT X.name,X.date,X .salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
>
> SELECT X.name,Min(X.da te),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?
>
> Regards,
> Aamir[/color]

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,da te) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Hugo Kornelis]

Re: A basic question: Removing duplicate results from Max function

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

(snip)[color=blue]
>I tried
>
>SELECT X.name,Min(X.da te),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?[/color]

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.da te),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

[aamircheema@gmail.com]

Re: A basic question: Removing duplicate results from Max function



Thanks Everybody. That helped a lot