SQL group and ordering by

Question

Well, I have a SQL table that is as below:

Name title dept salary
Abc aaaa qazwsx 15
Abc aaaa hehene 73
Abc aaaa geneh 55
Abc bbbb thghbh 33
Abc bbbb hehehe 433
Abc bbbb eyehhe 54

Statement to create data -

CREATE TABLE #StackOverflow
(Name VARCHAR(5)
,Title VARCHAR(5)
, Dept VARCHAR(10)
, Salary INT);

INSERT INTO #StackOverflow
(Name, Title, Dept, Salary)
SELECT 'Abc', 'aaaa', 'qazwsx', 15  UNION ALL
SELECT 'Abc', 'aaaa', 'hehene', 73  UNION ALL
SELECT 'Abc', 'aaaa', 'geneh', 55   UNION ALL
SELECT 'Abc', 'bbbb', 'thghbh', 33  UNION ALL
SELECT 'Abc', 'bbbb', 'hehehe', 433 UNION ALL
SELECT 'Abc', 'bbbb', 'eyehhe', 54

I need to write a query which will give me the top 2 depts whose name are abc and title is aaaa , whose name is abc and title is bbbb. This should be ordered by salary!

How do I implement it using SQL sub queries?

Please show some more effort... What have you tried already? — Shnugo, Aug 20 '15 at 21:38

M.Ali · Answer 1 · 2015-08-20T21:51:46.367

2

;WITH X AS 
 (
  SELECT * 
       ,ROW_NUMBER() OVER (PARTITION BY Name, Title
                           ORDER BY Salary DESC) rn 
  FROM TableName 
 )
SELECT * 
FROM x 
WHERE rn <= 2

edited Aug 20 '15 at 21:51

answered Aug 20 '15 at 21:39

M.Ali

67,945
13
101
127

The question asks for top 2 for each name, title combination I believe. – Martin Smith Aug 20 '15 at 21:47
cheers @MartinSmith I misread the question :) – M.Ali Aug 20 '15 at 21:52

asdf · Answer 2 · 2015-08-20T21:44:14.330

-1

SELECT TOP 2 dept
   FROM table 
   WHERE name='abc' AND title='aaaa'
   ORDER BY salary DESC;

edited Aug 20 '15 at 21:44

answered Aug 20 '15 at 21:38

asdf

2,927
2
21
42

SQL group and ordering by

2 Answers2