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

On 10 Jun 2006 07:48:38 -0700, aamircheema@xxxxxxxxx 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
.

Relevant Pages

  • Re: Rqst for Inventory Database Best Practices
    ... Hugo Kornelis, SQL Server MVP ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Nested select
    ... Order column is NOT NULL...) ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Reduce Table Size without deleting data
    ... On Thu, 20 Jul 2006 14:23:50 GMT, iamset via SQLMonster.com wrote: ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Help with Query
    ... Please don't post copies of the same question to multiple locations; ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Angband 3.0.7s3
    ... Another, probably mildly annoying, release of Angband "3.0.7s". ... really almost entirely other people's work; thanks go to Hugo Kornelis, ... Pete Mack, ... <SNIP> ...
    (rec.games.roguelike.angband)