1

Suppose that I have a table in a SQL database with columns like the ones shown below. The table records various performance metrics of the employees in my company each month.

I can easily query the table so that I can see the best monthly sales figures that my employees have ever obtained, along with which employee was responsible and which month the figure was obtained in:

SELECT * FROM EmployeePerformance ORDER BY Sales DESC;

NAME    MONTH   SALES      COMMENDATIONS  ABSENCES    
Karen   Jul 16  36,319.13  2              0
David   Feb 16  35,398.03  2              1
Martin  Nov 16  33,774.38  1              1
Sandra  Nov 15  33,012.55  4              0
Sandra  Mar 16  31,404.45  1              0
Karen   Sep 16  30,645.78  2              2
David   Feb 16  29,584.81  1              1
Karen   Jun 16  29,030.00  3              0
Stuart  Mar 16  28,877.34  0              1
Karen   Nov 15  28,214.42  1              2
Martin  May 16  28,091.99  3              0

This query is very simple, but it's not quite what I want. How would I need to change it if I wanted to see only the top 3 monthly figures achieved by each employee in the result set?

To put it another way, I want to write a query that is the same as the one above, but if any employee would appear in the result set more than 3 times, then only their top 3 results should be included, and any further results of theirs should be ignored. In my sample query, Karen's figure from Nov 15 would no longer be included, because she already has three other figures higher than that according to the ordering "ORDER BY Sales DESC".

The specific SQL database I am using is either SQLite or, if what I need is not possible with SQLite, then MySQL.

utterly confuzzled
  • 103
  • 1
  • 9

2 Answers2

0

In MySQL you can use windows function:

SELECT *
FROM EmployeePerformance
WHERE row_number() OVER (ORDER BY Sales DESC)<=3
ORDER BY Sales DESC

In SQLite window functions aren't available, but you still can count the preceding rows:

SELECT *
FROM EmployeePerformance e
WHERE
  (SELECT COUNT(*)
   FROM EmployeePerformance ee
   WHERE ee.Name=e.Name and ee.Sales>e.Sales)<3
ORDER BY e.Sales DESC
Dmitry Kurbsky
  • 131
  • 4
0

I have managed to find an answer myself. It seems to work by pairing each record up with all of the records from the same person that were equal or greater, and then choosing only the (left) records that had no more than 3 greater-or-equal pairings.

SELECT P.Name, P.Month, P.Sales, P.Commendations, P.Absences
FROM Performance P
LEFT JOIN Performance P2 ON (P.Name = P2.Name AND P.Sales <= P2.Sales)
GROUP BY P.Name, P.Month, P.Sales, P.Commendations, P.Absences
HAVING COUNT(*) <= 3
ORDER BY P.Sales DESC;

I will give the credit to a_horse_with_no_name for adding the tag "greatest-n-per-group", as I would have had no idea what to search for otherwise, and by looking through other questions with this tag I managed to find what I wanted.

I found this question that was similar to mine... Using LIMIT within GROUP BY to get N results per group?

And I followed this link that somebody had included in a comment... https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

...and the answer I wanted was in the first comment on that article. It's perfect as it uses only a LEFT JOIN, so it will work in SQLite.

Here is my SQL Fiddle: http://sqlfiddle.com/#!7/580f0/5/0

Community
  • 1
  • 1
utterly confuzzled
  • 103
  • 1
  • 9