Select top 10 records for each category

Question

I want to return top 10 records from each section in one query  Can anyone help with how to do it   Section is one of the columns in the table   Database is SQL Server 2005  I want to return the top 10 by date entered   Sections are business  local  and feature   For one particular date I want only the top  10  business rows  most recent entry   the top  10  local rows  and the top  10  features

User · Answer

This works on SQL Server 2005  edited to reflect your clarification    select   from Things t where t ThingID in       select top 10 ThingID     from Things tt     where tt Section   t Section and tt ThingDate    Date     order by tt DateEntered desc           and t ThingDate    Date order by Section  DateEntered desc

User · Answer

You can try this approach   This query returns 10 most populated cities for each country      SELECT city  country  population    FROM     SELECT city  country  population       country rank    IF  current country   country   country rank   1  1  AS country rank      current country    country     FROM cities    ORDER BY country  population DESC      ranked    WHERE country rank  lt   10

User · Answer

In T-SQL  I would do   WITH TOPTEN AS       SELECT    ROW NUMBER        over           PARTITION BY  group by field           order by  prioritise field        AS RowNo      FROM  table name    SELECT   FROM TOPTEN WHERE RowNo  lt   10

User · Answer

If you want to produce output grouped by section  displaying only the top n records from each section something like this   SECTION     SUBSECTION  deer        American Elk Wapiti deer        Chinese Water Deer dog         Cocker Spaniel dog         German Shephard horse       Appaloosa horse       Morgan      then the following should work pretty generically with all SQL databases   If you want the top 10  just change the 2 to a 10 toward the end of the query   select     x1 section       x1 subsection from example x1 where           select count        from example x2     where x2 section   x1 section     and x2 subsection  lt   x1 subsection        lt   2 order by section  subsection    To set up   create table example   id int  section varchar 25   subsection varchar 25      insert into example select 0   dog    Labrador Retriever   insert into example select 1   deer    Whitetail   insert into example select 2   horse    Morgan   insert into example select 3   horse    Tarpan   insert into example select 4   deer    Row   insert into example select 5   horse    Appaloosa   insert into example select 6   dog    German Shephard   insert into example select 7   horse    Thoroughbred   insert into example select 8   dog    Mutt   insert into example select 9   horse    Welara Pony   insert into example select 10   dog    Cocker Spaniel   insert into example select 11   deer    American Elk Wapiti   insert into example select 12   horse    Shetland Pony   insert into example select 13   deer    Chinese Water Deer   insert into example select 14   deer    Fallow

User · Answer

Tried the following and it worked with ties too   SELECT rs Field1 rs Field2  FROM       SELECT Field1 Field2  ROW NUMBER          OVER  Partition BY Section             ORDER BY RankCriteria DESC   AS Rank     FROM table       rs WHERE Rank  lt   10

User · Answer

I do it this way   SELECT a   FROM articles AS a   LEFT JOIN articles AS a2      ON a section   a2 section AND a article date  lt   a2 article date GROUP BY a article id HAVING COUNT     lt   10      update   This example of GROUP BY works in MySQL and SQLite only  because those databases are more permissive than standard SQL regarding GROUP BY   Most SQL implementations require that all columns in the select-list that aren t part of an aggregate expression are also in the GROUP BY

User · Answer

In T-SQL  I would do   WITH TOPTEN AS       SELECT    ROW NUMBER        over           PARTITION BY  group by field           order by  prioritise field        AS RowNo      FROM  table name    SELECT   FROM TOPTEN WHERE RowNo  lt   10

User · Answer

If you are using SQL 2005 you can do something like this     SELECT rs Field1 rs Field2      FROM           SELECT Field1 Field2  Rank              over  Partition BY Section                 ORDER BY RankCriteria DESC   AS Rank         FROM table           rs WHERE Rank  lt   10   If your RankCriteria has ties then you may return more than 10 rows and Matt s solution may be better for you

User · Answer

If we use SQL Server    2005  then we can solve the task with one select only   declare  t table       Id      int       Section int      Moment  date     insert into  t values     1       1      2014-01-01        2       1      2014-01-02        3       1      2014-01-03        4       1      2014-01-04        5       1      2014-01-05         6       2      2014-02-06        7       2      2014-02-07        8       2      2014-02-08        9       2      2014-02-09        10      2      2014-02-10         11      3      2014-03-11        12      3      2014-03-12        13      3      2014-03-13        14      3      2014-03-14        15      3      2014-03-15      -- TWO earliest records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment   lt   2          then 0          else 1      end    -- THREE earliest records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment   lt   3          then 0          else 1      end    -- three LATEST records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment desc   lt   3          then 0          else 1      end

User · Answer

If you know what the sections are  you can do   select top 10   from table where section 1 union select top 10   from table where section 2 union select top 10   from table where section 3

User · Answer

Might the UNION operator work for you  Have one SELECT for each section  then UNION them together  Guess it would only work for a fixed number of sections though

User · Answer

While the question was about SQL Server 2005  most people have moved on and if they do find this question  what could be the preferred answer in other situations is one using CROSS APPLY as illustrated in this blog post   SELECT   FROM t CROSS APPLY     SELECT TOP 10 u     FROM u   WHERE u t id   t t id   ORDER BY u something DESC   u   This query involves 2 tables  The OP s query only involves 1 table  in case of which a window function based solution might be more efficient

User · Answer

I know this thread is a little bit old but I ve just bumped into a similar problem  select the newest article from each category  and this is the solution I came up with    WITH  TopCategoryArticles  AS       SELECT           ArticleID           ROW NUMBER   OVER               PARTITION BY  ArticleCategoryID              ORDER BY  ArticleDate  DESC           AS  Order      FROM  dbo   Articles    SELECT  Articles     FROM       TopCategoryArticles  LEFT JOIN       dbo   Articles  ON          TopCategoryArticles   ArticleID     Articles   ArticleID  WHERE  TopCategoryArticles   Order    1   This is very similar to Darrel s solution but overcomes the RANK problem that might return more rows than intended

User · Answer

SELECT r   FROM       SELECT         r            ROW NUMBER   OVER PARTITION BY r  SectionID                            ORDER BY r  DateEntered  DESC  rn     FROM  Records  r   r WHERE r rn  lt   10 ORDER BY r  DateEntered  DESC

User · Answer

I do it this way   SELECT a   FROM articles AS a   LEFT JOIN articles AS a2      ON a section   a2 section AND a article date  lt   a2 article date GROUP BY a article id HAVING COUNT     lt   10      update   This example of GROUP BY works in MySQL and SQLite only  because those databases are more permissive than standard SQL regarding GROUP BY   Most SQL implementations require that all columns in the select-list that aren t part of an aggregate expression are also in the GROUP BY

User · Answer

SELECT r   FROM       SELECT         r            ROW NUMBER   OVER PARTITION BY r  SectionID                            ORDER BY r  DateEntered  DESC  rn     FROM  Records  r   r WHERE r rn  lt   10 ORDER BY r  DateEntered  DESC

User · Answer

If you are using SQL 2005 you can do something like this     SELECT rs Field1 rs Field2      FROM           SELECT Field1 Field2  Rank              over  Partition BY Section                 ORDER BY RankCriteria DESC   AS Rank         FROM table           rs WHERE Rank  lt   10   If your RankCriteria has ties then you may return more than 10 rows and Matt s solution may be better for you

User · Answer

If you know what the sections are  you can do   select top 10   from table where section 1 union select top 10   from table where section 2 union select top 10   from table where section 3

User · Answer

Might the UNION operator work for you  Have one SELECT for each section  then UNION them together  Guess it would only work for a fixed number of sections though

User · Answer

This works on SQL Server 2005  edited to reflect your clarification    select   from Things t where t ThingID in       select top 10 ThingID     from Things tt     where tt Section   t Section and tt ThingDate    Date     order by tt DateEntered desc           and t ThingDate    Date order by Section  DateEntered desc

User · Answer

If you are using SQL 2005 you can do something like this     SELECT rs Field1 rs Field2      FROM           SELECT Field1 Field2  Rank              over  Partition BY Section                 ORDER BY RankCriteria DESC   AS Rank         FROM table           rs WHERE Rank  lt   10   If your RankCriteria has ties then you may return more than 10 rows and Matt s solution may be better for you

User · Answer

If you know what the sections are  you can do   select top 10   from table where section 1 union select top 10   from table where section 2 union select top 10   from table where section 3

User · Answer

If we use SQL Server    2005  then we can solve the task with one select only   declare  t table       Id      int       Section int      Moment  date     insert into  t values     1       1      2014-01-01        2       1      2014-01-02        3       1      2014-01-03        4       1      2014-01-04        5       1      2014-01-05         6       2      2014-02-06        7       2      2014-02-07        8       2      2014-02-08        9       2      2014-02-09        10      2      2014-02-10         11      3      2014-03-11        12      3      2014-03-12        13      3      2014-03-13        14      3      2014-03-14        15      3      2014-03-15      -- TWO earliest records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment   lt   2          then 0          else 1      end    -- THREE earliest records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment   lt   3          then 0          else 1      end    -- three LATEST records in each Section  select top 1 with ties     Id  Section  Moment  from      t order by      case          when row number   over partition by Section order by Moment desc   lt   3          then 0          else 1      end

User · Answer

This works on SQL Server 2005  edited to reflect your clarification    select   from Things t where t ThingID in       select top 10 ThingID     from Things tt     where tt Section   t Section and tt ThingDate    Date     order by tt DateEntered desc           and t ThingDate    Date order by Section  DateEntered desc

User · Answer

I do it this way   SELECT a   FROM articles AS a   LEFT JOIN articles AS a2      ON a section   a2 section AND a article date  lt   a2 article date GROUP BY a article id HAVING COUNT     lt   10      update   This example of GROUP BY works in MySQL and SQLite only  because those databases are more permissive than standard SQL regarding GROUP BY   Most SQL implementations require that all columns in the select-list that aren t part of an aggregate expression are also in the GROUP BY

User · Answer

If you are using SQL 2005 you can do something like this     SELECT rs Field1 rs Field2      FROM           SELECT Field1 Field2  Rank              over  Partition BY Section                 ORDER BY RankCriteria DESC   AS Rank         FROM table           rs WHERE Rank  lt   10   If your RankCriteria has ties then you may return more than 10 rows and Matt s solution may be better for you

User · Answer

Tried the following and it worked with ties too   SELECT rs Field1 rs Field2  FROM       SELECT Field1 Field2  ROW NUMBER          OVER  Partition BY Section             ORDER BY RankCriteria DESC   AS Rank     FROM table       rs WHERE Rank  lt   10

User · Answer

I know this thread is a little bit old but I ve just bumped into a similar problem  select the newest article from each category  and this is the solution I came up with    WITH  TopCategoryArticles  AS       SELECT           ArticleID           ROW NUMBER   OVER               PARTITION BY  ArticleCategoryID              ORDER BY  ArticleDate  DESC           AS  Order      FROM  dbo   Articles    SELECT  Articles     FROM       TopCategoryArticles  LEFT JOIN       dbo   Articles  ON          TopCategoryArticles   ArticleID     Articles   ArticleID  WHERE  TopCategoryArticles   Order    1   This is very similar to Darrel s solution but overcomes the RANK problem that might return more rows than intended

User · Answer

I do it this way   SELECT a   FROM articles AS a   LEFT JOIN articles AS a2      ON a section   a2 section AND a article date  lt   a2 article date GROUP BY a article id HAVING COUNT     lt   10      update   This example of GROUP BY works in MySQL and SQLite only  because those databases are more permissive than standard SQL regarding GROUP BY   Most SQL implementations require that all columns in the select-list that aren t part of an aggregate expression are also in the GROUP BY

User · Answer

Might the UNION operator work for you  Have one SELECT for each section  then UNION them together  Guess it would only work for a fixed number of sections though

User · Answer

Q  Finding TOP X records from each group Oracle   SQL gt  select   from emp e    2  where e empno in  select d empno from emp d    3  where d deptno e deptno and rownum lt 3    4  order by deptno   5      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10   7839 KING       PRESIDENT            17-NOV-81       5000                    10   7369 SMITH      CLERK           7902 17-DEC-80        800                    20   7566 JONES      MANAGER         7839 02-APR-81       2975                    20   7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30   7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30   6 rows selected

User · Answer

If you know what the sections are  you can do   select top 10   from table where section 1 union select top 10   from table where section 2 union select top 10   from table where section 3

User · Answer

Might the UNION operator work for you  Have one SELECT for each section  then UNION them together  Guess it would only work for a fixed number of sections though

User · Answer

This works on SQL Server 2005  edited to reflect your clarification    select   from Things t where t ThingID in       select top 10 ThingID     from Things tt     where tt Section   t Section and tt ThingDate    Date     order by tt DateEntered desc           and t ThingDate    Date order by Section  DateEntered desc

User · Answer

You can try this approach   This query returns 10 most populated cities for each country      SELECT city  country  population    FROM     SELECT city  country  population       country rank    IF  current country   country   country rank   1  1  AS country rank      current country    country     FROM cities    ORDER BY country  population DESC      ranked    WHERE country rank  lt   10

User · Answer

While the question was about SQL Server 2005  most people have moved on and if they do find this question  what could be the preferred answer in other situations is one using CROSS APPLY as illustrated in this blog post   SELECT   FROM t CROSS APPLY     SELECT TOP 10 u     FROM u   WHERE u t id   t t id   ORDER BY u something DESC   u   This query involves 2 tables  The OP s query only involves 1 table  in case of which a window function based solution might be more efficient

User · Answer

Q  Finding TOP X records from each group Oracle   SQL gt  select   from emp e    2  where e empno in  select d empno from emp d    3  where d deptno e deptno and rownum lt 3    4  order by deptno   5      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10   7839 KING       PRESIDENT            17-NOV-81       5000                    10   7369 SMITH      CLERK           7902 17-DEC-80        800                    20   7566 JONES      MANAGER         7839 02-APR-81       2975                    20   7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30   7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30   6 rows selected

User · Answer

If you want to produce output grouped by section  displaying only the top n records from each section something like this   SECTION     SUBSECTION  deer        American Elk Wapiti deer        Chinese Water Deer dog         Cocker Spaniel dog         German Shephard horse       Appaloosa horse       Morgan      then the following should work pretty generically with all SQL databases   If you want the top 10  just change the 2 to a 10 toward the end of the query   select     x1 section       x1 subsection from example x1 where           select count        from example x2     where x2 section   x1 section     and x2 subsection  lt   x1 subsection        lt   2 order by section  subsection    To set up   create table example   id int  section varchar 25   subsection varchar 25      insert into example select 0   dog    Labrador Retriever   insert into example select 1   deer    Whitetail   insert into example select 2   horse    Morgan   insert into example select 3   horse    Tarpan   insert into example select 4   deer    Row   insert into example select 5   horse    Appaloosa   insert into example select 6   dog    German Shephard   insert into example select 7   horse    Thoroughbred   insert into example select 8   dog    Mutt   insert into example select 9   horse    Welara Pony   insert into example select 10   dog    Cocker Spaniel   insert into example select 11   deer    American Elk Wapiti   insert into example select 12   horse    Shetland Pony   insert into example select 13   deer    Chinese Water Deer   insert into example select 14   deer    Fallow

[sql] Select top 10 records for each category

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to greatest-n-per-group