How do i get top n records of each category

Question

Hi guys,

I am here to get records based on categories.

My table foo has fields [id, name, class]. my records can be like:

1, ram, 10
2, hari, 9
3, sita, 10
4, gita, 9
5, rita, 5
6, tina, 7
8, nita, 8
9, bita, 5
10,seta, 7

...and more...

Now i would like to get result with each record from different class.. i.e something like

1, ram, 10
2, hari, 9
5, rita, 5
6, tina, 7
8, nita, 8

i.e just top 1 records as per class

Answer 1

For SQL Server 2005+ and Oracle 9i+, use analytic functions:

WITH summary AS (
  SELECT f.id,
         f.name,
         f.class,
         ROW_NUMBER() OVER (PARTITION BY f.class
                                ORDER BY f.name) AS rank
    FROM FOO f)
SELECT s.id,
       s.name,
       s.class
  FROM summary s
 WHERE s.rank = 1

This also uses a Common Table Expression (CTE), known as Subquery Factoring in Oracle...

MySQL doesn't have analytic function support, so you have to use:

SELECT x.id,
       x.name,
       x.class
  FROM (SELECT f.id,
               f.name,
               f.class,
               CASE 
                 WHEN @class = f.class THEN @rownum := @rownum + 1 
                 ELSE @rownum := 1
               END AS rank,
               @class := f.class
          FROM FOO f
          JOIN (SELECT @rownum := 0, @class := '') r
      ORDER BY f.class, f.name) x
 WHERE x.rank = 1

Answer 2

With SQL Server or Oracle (or any other engine implementing that part of the standard, including e.g. PostgreSQL among the free ones), the "window functions" in an OVER clause (e.g., see here for MS's docs about them) make it easy; e.g., in this SO question, see @Darrel's answer (he's selecting the top 10 per category, you only want the top 1, the changes should be obvious;-).

In MySql, or other engine not complying with the standard regarding the OVER clause, you could use @Bill's answer (good for MySql, not for others) or @Matt's (may need slight adaptation since he's answering for SQL Server and so using SELECT TOP 10 ... -- in MySql that would be SELECT ... LIMIT 10!-).

Answer 3

I tested in sql 2008 this and works for me, hope that helps you in some way.

DECLARE @Class TABLE
(
    id INT
    ,Name NVARCHAR(120)
    ,Class INT

    PRIMARY KEY (id)
)

INSERT INTO @Class values (1, 'ram', 10)
INSERT INTO @Class values (2, 'hari', 9)
INSERT INTO @Class values (3, 'sita', 10)
INSERT INTO @Class values (4, 'gita', 9)
INSERT INTO @Class values (5, 'rita', 5)
INSERT INTO @Class values (6, 'tina', 7)
INSERT INTO @Class values (8, 'nita', 8)
INSERT INTO @Class values (9, 'bita', 5)
INSERT INTO @Class values (10, 'seta', 7)

SELECT A.id, A.Name, A.Class
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Class ORDER BY ID) as Num, ID, Name, Class
    FROM @Class
) A
WHERE A.Num = 1
ORDER BY id

Answer 4

This should be the easiest way, which doesn't involve any database-specific options:

select * 
  from foo 
 where id in (select min(id) 
                from foo 
               group by class);

upd: yeah, of course this would work only if you need only one record from each class.

upd2: just for fun come up with a query thta shows you TOP N and doesn't involve analytics. looks kinda messy, but seems to work :)

select newfoo.id, newfoo.name, newfoo.class
  from (select class, max(r) top, min(r) bottom
          from (select f.*, rownum r
                  from (select id, name, class from foo order by class, id asc) f)
         group by class) minmax,
       (select id, name, class, r
          from (select f.*, rownum r
                  from (select id, name, class from foo order by class, id asc) f)) newfoo
 where newfoo.class = minmax.class
   and newfoo.r between minmax.bottom and
       least(minmax.bottom + (TOP_N-1), minmax.top);

where TOP_N is amount of records you need to get.

Answer 5

Here is another way

    DECLARE @foo TABLE(ID INT,Name VARCHAR(20),Class INT)
INSERT INTO @foo
SELECT 1,'ram', 10 UNION ALL
SELECT 2, 'hari', 9 UNION ALL 
SELECT 3, 'sita', 10  UNION ALL
SELECT 4, 'gita', 9  UNION ALL
SELECT 5, 'rita', 5  UNION ALL
SELECT 6, 'tina', 7  UNION ALL
SELECT 8, 'nita', 8  UNION ALL
SELECT 9, 'bita', 5  UNION ALL
SELECT 10,'seta', 7

SELECT DISTINCT X.*
FROM @foo f
CROSS APPLY(SELECT TOP 1 * FROM @foo WHERE Class = f.Class) AS X