By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Top n by category

P: 4
For simplcity sake - I developed a table of the following:

Name Salary Sport
Roger Clemens 28,000,000 Baseball
David Ortiz 1 8,000,000 Baseball
Kobe Bryant 15,000,000 Basketball
Phil Mickelson 20,000,000 Golf
Tiger Woods 30,000,000 Golf

I am trying to queries the top salary per sport.

Results should be:
Name Salary Sport
Roger Clemens 28000000 Baseball
Kobe Bryant 15000000 Basketball
Tiger Woods 30000000 Golf

HELP???
May 21 '07 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,619
For simplcity sake - I developed a table of the following:

Name Salary Sport
Roger Clemens 28,000,000 Baseball
David Ortiz 1 8,000,000 Baseball
Kobe Bryant 15,000,000 Basketball
Phil Mickelson 20,000,000 Golf
Tiger Woods 30,000,000 Golf

I am trying to queries the top salary per sport.

Results should be:
Name Salary Sport
Roger Clemens 28000000 Baseball
Kobe Bryant 15000000 Basketball
Tiger Woods 30000000 Golf

HELP???
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary
  2. FROM tblSports
  3. GROUP BY tblSports.Sport
  4. ORDER BY tblSports.Sport;
May 21 '07 #2

P: 4
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary
  2. FROM tblSports
  3. GROUP BY tblSports.Sport
  4. ORDER BY tblSports.Sport;

It worked to a point, but how do i also include the name too?
May 21 '07 #3

P: 4
It worked to a point, but how do i also include the name too?
how do i also include the name ?
May 21 '07 #4

P: 4
For simplcity sake - I developed a table of the following:

Name Salary Sport
Roger Clemens 28,000,000 Baseball
David Ortiz 1 8,000,000 Baseball
Kobe Bryant 15,000,000 Basketball
Phil Mickelson 20,000,000 Golf
Tiger Woods 30,000,000 Golf

I am trying to queries the top salary per sport.

Results should be:
Name Salary Sport
Roger Clemens 28000000 Baseball
Kobe Bryant 15000000 Basketball
Tiger Woods 30000000 Golf

I tried the below, but it does not give mme the specific name per sport.


Code: ( sql )
SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary
FROM tblSports
GROUP BY tblSports.Sport
ORDER BY tblSports.Sport;

Need help -quick....
May 21 '07 #5

NeoPa
Expert Mod 15k+
P: 31,342
Jamie,
Please be patient. I will get to this later today when I get an opportunity.
I have an answer but I need to sort the details out before submitting it.
May 22 '07 #6

NeoPa
Expert Mod 15k+
P: 31,342
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Sport],
  2.        Max([Salary]) AS MaxSalary,
  3.        Mid(Max(Format([Salary],'0000000000') & [Name]),11,99) AS [MaxName]
  4. FROM [tblSports]
  5. GROUP BY [Sport]
  6. ORDER BY [Sport]
In line 3, the number of zeroes must be 1 fewer than the second parameter of Mid() (in this case 11).
Also in line 3, the third parameter of Mid() (in this case 99), can be the length of the name field, but I don't know what that is.
May 22 '07 #7

Rabbit
Expert Mod 10K+
P: 12,347
For simplcity sake - I developed a table of the following:

Name Salary Sport
Roger Clemens 28,000,000 Baseball
David Ortiz 1 8,000,000 Baseball
Kobe Bryant 15,000,000 Basketball
Phil Mickelson 20,000,000 Golf
Tiger Woods 30,000,000 Golf

I am trying to queries the top salary per sport.

Results should be:
Name Salary Sport
Roger Clemens 28000000 Baseball
Kobe Bryant 15000000 Basketball
Tiger Woods 30000000 Golf

I tried the below, but it does not give mme the specific name per sport.


Code: ( sql )
SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary
FROM tblSports
GROUP BY tblSports.Sport
ORDER BY tblSports.Sport;

Need help -quick....
This might not be the exact names of your fields and all but...
Expand|Select|Wrap|Line Numbers
  1. SELECT x.Sport, x.Name, x.Salary
  2. FROM tblSports As x
  3. WHERE x.Name = (SELECT TOP 1 Name FROM tblSports WHERE Sport = x.Sport ORDER BY Salary;);
May 22 '07 #8

NeoPa
Expert Mod 15k+
P: 31,342
This might not be the exact names of your fields and all but...
Expand|Select|Wrap|Line Numbers
  1. SELECT x.Sport, x.Name, x.Salary
  2. FROM tblSports As x
  3. WHERE x.Name = (SELECT TOP 1 [Name]
  4.                 FROM tblSports
  5.                 WHERE Sport = x.Sport
  6.                 ORDER BY Salary);
I don't know whether or not I like this better than the previous offering (mine ;)), but I certainly like it a lot :) Very nice Rabbit.

BTW I removed the ';' in the subquery as that would stop it working (I'm pretty sure).
May 23 '07 #9

ADezii
Expert 5K+
P: 8,619
I don't know whether or not I like this better than the previous offering (mine ;)), but I certainly like it a lot :) Very nice Rabbit.

BTW I removed the ';' in the subquery as that would stop it working (I'm pretty sure).
Compliments to both you and Rabbit on your impressive solutions!
May 24 '07 #10

Rabbit
Expert Mod 10K+
P: 12,347
I don't know whether or not I like this better than the previous offering (mine ;)), but I certainly like it a lot :) Very nice Rabbit.

BTW I removed the ';' in the subquery as that would stop it working (I'm pretty sure).
I like subqueries lol.
May 24 '07 #11

NeoPa
Expert Mod 15k+
P: 31,342
I like subqueries lol.
Me too. Definitely.
Sometimes they can be used because the coder is unable to set the SQL up properly, which is not so good, but otherwise they can be very useful & very powerful.
In this case, the more I think about it, the more I prefer your solution.
I especially like that you introduce me to ways of using the SQs I hadn't considered before :) Particularly the linking of the SQ to the outer query.
May 24 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.