464,660 Members | 1,316 Online
Need help? Post your question and get tips & solutions from a community of 464,660 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
11 Replies

 Expert 5K+ P: 8,750 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 SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary FROM tblSports GROUP BY tblSports.Sport ORDER BY tblSports.Sport; May 21 '07 #2

 P: 4 Expand|Select|Wrap|Line Numbers SELECT tblSports.Sport, Max(tblSports.Salary) AS Maximum_Salary FROM tblSports GROUP BY tblSports.Sport 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

 Expert Mod 15k+ P: 31,770 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

 Expert Mod 15k+ P: 31,770 Try : Expand|Select|Wrap|Line Numbers SELECT [Sport],        Max([Salary]) AS MaxSalary,        Mid(Max(Format([Salary],'0000000000') & [Name]),11,99) AS [MaxName] FROM [tblSports] GROUP BY [Sport] 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

 Expert Mod 10K+ P: 12,441 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 SELECT x.Sport, x.Name, x.Salary FROM tblSports As x WHERE x.Name = (SELECT TOP 1 Name FROM tblSports WHERE Sport = x.Sport ORDER BY Salary;); May 22 '07 #8

 Expert Mod 15k+ P: 31,770 This might not be the exact names of your fields and all but... Expand|Select|Wrap|Line Numbers SELECT x.Sport, x.Name, x.Salary FROM tblSports As x WHERE x.Name = (SELECT TOP 1 [Name]                 FROM tblSports                 WHERE Sport = x.Sport                 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

 Expert 5K+ P: 8,750 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

 Expert Mod 10K+ P: 12,441 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

 Expert Mod 15k+ P: 31,770 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