How to get highest bonus and second highest bnus paid freelancer for each group
-
I have a table below:
Freelancer(id, fl_name, bonus, fl_group)
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')I have been asked to provide a query to get highest bonus and second highest bonus paid freelancer for each group.
Restriction:
No any analytical function should be used in the query and no temp table.
Don't how to write the query without using analytical functions like rowcount, rank, max, etc.
Thank you very much in advance.
- This topic was modified 4 years, 10 months ago by PasLe Choix.
- This topic was modified 4 years, 10 months ago by PasLe Choix.
- This topic was modified 4 years, 10 months ago by PasLe Choix.
- This topic was modified 4 years, 10 months ago by PasLe Choix.
-
This sounds very much like a homework or interview question.
I would suggest researching the TOP key word
-
Your test data only has one person per group.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry, my bad, sample table updated slightly.
-
Heh... can you put the Create Table statement back in?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
and where's your CREATE TABLE script?
TOP and CROSS APPLY... and absent another table, you might need DISTINCT.
Post your attempt... if we just tell you the answer, you won't learn anything.
-
added back. sorry again.
-
This query should work, please try it.
WITH CTE AS
(
SELECT *, DENSE_RANK() OVER(PARTITION BY Fl_Group Order by Bonus DESC) AS H_Bonus FROM Freelancer
)
SELECT * FROM CTE WHERE H_Bonus<3 -
Thanks.
Restriction:
No any analytical function should be used in the query and no temp table.
-
Which analytic function has violated your restriction? What is the reason for the restriction? If, as I suspect, it's because this is a homework question, please show us what you've already tried, and we'll try to point out where you're going wrong.
John
-
Thanks John,
DENSE_RANK() is an analytical function
Below is what I tried:
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
The output contains more records I need, each group should have only two records (top 2)
Thank you.
-
SELECT u.*
FROM (SELECT DISTINCT fl_group
FROM Freelancer t) t
CROSS APPLY(SELECT TOP(2) *
FROM Freelancer u
WHERE u.fl_group=t.fl_group
ORDER BY u.bonus DESC) u -
Thank you Jonathan for the answer, it returns expected result: top 2 for each group, that's what I want.
- PasLe Choix wrote:
Thanks John,
DENSE_RANK() is an analytical function
No it isn't, it's a Window function.
John already provided a link to analytic functions. Had you followed it, you would have seen that DENSE_RANK() does not appear there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Cross post on Stack Overflow, if anyone interested.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply