Select top 10 records for each category

Question

I want to return the top 10 records from each section in one query. Can anyone help with how to do it? The section is one of the columns in the table.

The 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.

Answer 1

You can use the following T-SQL query to get the top 10 records for each category:

Row_number() is a window function that returns the count for each group by category 

WITH Top_Ten as (

    SELECT *, ROW_NUMBER()

    over (

        PARTITION BY [group_by_field]

        order by [prioritise_field]

    ) AS Row_No

    FROM table_name

)

From the created Top_ten table, we have to retrieve the records whose row_no is less than 10.

SELECT * FROM Top_Ten WHERE Row_No <= 10

If you want to learn more about T-SQL and window functions, you can enroll in SQL online course by Intellipaat.