Select Top 10 Records For Each Category

How to Select top n Records for Each Category

not tested, but I would try something like:

with
basedata as (    
select EmployeeName
     , CompanyNumber
     , COUNT(ID) as Number_of_Deals
  from Deal
  join DealEmployee
    on Deal.DealID = DealEmployee.DealID    
 where Deal.Status = 2
   and Date between '2016-01-01' and '2017-12-31'
   and EmployeeName !=''
 group by EmployeeName
        , CompanyNumber 
)
,
basedata_with_rank as (
select t.*
     , row_number() over (partition by CompanyNumber order by Number_of_Deals desc) rn
  from basedata
)
select *
  from basedata_with_rank
 where rn <= 5
 order by CompanyNumber
        , Number_of_Deals desc

Using CTEs makes queries usually more readable. By the way: I would avoid to give a column the name "date" (a reserved word) and I would always use qualified names for my columns. Perhaps the use of the analytic functions rank or dense rank would be more appropriate, but row_number should also work.

SELECT TOP 20 rows for each group

The easiest way would be to use the row_number() window function to number the rows for each city according to their visitnumber descending and use that as a filter. This query should work in any SQL Server version from 2005 onwards.

select * 
from (
    select *, r = row_number() over (partition by City order by VisitNumber desc) 
    from your_table
    ) a 
where r <= 20
  and City in ('Washington', 'New York', 'Los Angeles')

This would select the top 20 items for each city specified in the where clause.

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

maintain status in 2 places
preserve status history
...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

Select top 15 records from each group

You can use the DENSE_RANK() window function to assign group numbers to rows, and ROW_NUMBER() to assign a serial number within each group. Then, filtering is easy.

For example:

select *
from (
  select
    account_id, 
    transaction_id,
    dense_rank() over(order by account_id) as g,
    row_number() over(partition by account_id order by transaction_id) as rn
  from transactions
  where status = 'P'
) x
where g <= 10 -- the first 10 groups (accounts)
  and rn <= 15 -- the first 15 transactions within each group

Get top n records for each group of grouped results

Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries for each group:

(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)

There are a variety of ways to do this, see this article to determine the best route for your situation:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Edit:

This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 2:

select person, `group`, age
from 
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x 
where x.row_number <= 2;

See Demo

SQL get top 10 records for each category: avoid subqueries

I don't know of a way to do this without subqueries -- unless you use a semantic trick like "CTEs aren't subqueries" or "views" aren't subqueries.

Here is logic that does what you want, using a subquery:

select country, video, sum_views
from (Select country, video, sum(views) as sum_views,
             row_number() over (partition by country order by sum(views) desc) as seqnum
      From t1
      Group by 1, 2
     ) x
where seqnum <= 10;