Question posted in
The official documentation can be found here.

Redis – How to get top 2 for each category without using rank/row_number functions in ms-sql

I am on MS-SQL 2016.

I have a scenario that requires to get top 2 paid bonus for each group, RANK and ROW_NUMBER are not allowed, here is the code you can use to generate the table and data:

Create table Freelancer (id int, fl_name varchar(20), bonus int, fl_group varchar(50))


insert into Freelancer (id, fl_name, bonus, fl_group) Values (1, 'John', 1000, 'SQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (2, 'Jane', 990, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (3, 'Jimmy', 320, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (4, 'Jeff', 802, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (5, 'Johnathan', 2345, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (6, 'Jeffery', 321, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (7, 'Jane2', 1990, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (8, 'Jimmy3', 321, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (9, 'Jeff4', 803, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (10, 'Johnathan5', 345, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (11, 'Jeffery', 32, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (12, 'Jane3', 1190, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (13, 'Jimmy4', 322, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (14, 'Jeff5', 8002, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (15, 'Johnathan6', 235, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (16, 'Jeffery7', 31, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (17, 'Jack', 34, 'Redshift')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (18, 'Jennifer', 121, 'Aurora')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (19, 'Jackson', 425, 'Redis')

The expected result is for example in group DynamoDB, Jeff5(8002) and Jeff4(803) would be picked up

What I tried already is below and not returning the needed result despite I specified top 2 in the clause:

select *
from Freelancer t
where t.ID in (
    select top 2 ID
    from Freelancer tt
    where tt.fl_name = t.fl_name
    order by tt.bonus desc
    )
order by fl_group, bonus desc

my result:

enter image description here

Tags:

5

Answers


  1. 0

    You should be comparing bonuses between the outer query and subquery, and you should be correlating using the group.

    SELECT *
FROM Freelancer t
WHERE t.bonus IN (
    SELECT TOP 2 tt.bonus
    FROM Freelancer tt
    WHERE tt.fl_group = t.fl_group
    ORDER BY tt.bonus DESC
)
ORDER BY
    fl_group,
    bonus DESC;
    Login or Signup to reply.
  2. 0

    If you can use other windowed functions, try to replace ROW_NUMBER() behaviour with windowed SUM():

    SELECT 
    id, fl_name, bonus, fl_group
FROM (
    SELECT 
       *,
       SUM(1) OVER (PARTITION BY fl_group ORDER BY bonus) AS Rn
    FROM Freelancer
) t
WHERE Rn <= 2
    Login or Signup to reply.
  3. 0

    Use a correlated subquery which returns for each row the number of rows having greater bonus in that group:

    select t.* from Freelancer t
where 1 >= (select count(*) from Freelancer tt where tt.fl_group = t.fl_group and tt.bonus > t.bonus)
order by t.fl_group, t.bonus desc

    See the demo.
    Results:

    > id | fl_name    | bonus | fl_group
> -: | :--------- | ----: | :-------
> 18 | Jennifer   |   121 | Aurora  
> 14 | Jeff5      |  8002 | DynamoDB
>  9 | Jeff4      |   803 | DynamoDB
>  5 | Johnathan  |  2345 | Hive    
> 10 | Johnathan5 |   345 | Hive    
>  7 | Jane2      |  1990 | MySQL   
> 12 | Jane3      |  1190 | MySQL   
> 13 | Jimmy4     |   322 | Oracle  
>  8 | Jimmy3     |   321 | Oracle  
>  6 | Jeffery    |   321 | RDS     
> 11 | Jeffery    |    32 | RDS     
> 19 | Jackson    |   425 | Redis   
> 17 | Jack       |    34 | Redshift
>  1 | John       |  1000 | SQL
    Login or Signup to reply.
  4. 0

    You can use APPLY :

    SELECT DISTINCT f2.*
FROM Freelancer f CROSS APPLY
     ( SELECT TOP (2) f1.* 
       FROM Freelancer f1
       WHERE f1.fl_group = f.fl_group
       ORDER BY f1.bonus DESC
     ) f2
ORDER BY f2.fl_group, f2.bonus DESC;
    Login or Signup to reply.
  5. 0

    Please try this solution, hear in this solution I have consolidated fl_group as your category, and get fl_group wise top 2 records.

    Declare temp table 

    declare @Freelancer as TABLE (id int, fl_name varchar(20), bonus int, fl_group varchar(50))

    Insert Sample data 

    insert into @Freelancer(id, fl_name, bonus, fl_group) Values(1, 'John', 1000, 'SQL')
,(2, 'Jane', 990, 'MySQL')
,(3, 'Jimmy', 320, 'Oracle')
,(4, 'Jeff', 802, 'DynamoDB')
,(5, 'Johnathan', 2345, 'Hive')
,(6, 'Jeffery', 321, 'RDS')
,(7, 'Jane2', 1990, 'MySQL')
,(8, 'Jimmy3', 321, 'Oracle')
,(9, 'Jeff4', 803, 'DynamoDB')
,(10, 'Johnathan5', 345, 'Hive')
,(11, 'Jeffery', 32, 'RDS')
,(12, 'Jane3', 1190, 'MySQL')
,(13, 'Jimmy4', 322, 'Oracle')
,(14, 'Jeff5', 8002, 'DynamoDB')
,(15, 'Johnathan6', 235, 'Hive')
,(16, 'Jeffery7', 31, 'RDS')
,(17, 'Jack', 34, 'Redshift')
,(18, 'Jennifer', 121, 'Aurora')
,(19, 'Jackson', 425, 'Redis')

    Category-wise Top 2 Records 

    select *
from @Freelancer t
where t.ID in (
    select top 2 ID
    from @Freelancer tt
    where tt.fl_group = t.fl_group
    order by id
    )
order by fl_group,id

    Output

    Output

    Here, you can also use join instead of the subquery to improve your query performance.

    For More information, you can try this solution Please Visit this URL: Query Editor

    I hope this works for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search