Group By Count

Dear all

How do i group the count by Top ?

For example, We want to show Top 5 high salary people in my company , but may top 3 people's salary is same. So I want to sperate 5 groups, the output data look like this :
[Top 1 high salary] - Philip , Alex , David
[Top 2 high salary] - Emily
[Top 3 high salary] - Tom, May, Ada
[Top 4 high salary] - Queenie
[Top 5 high salary] - Ellie, Candy, Hazel, Stella, Tanya, Jacky

Hence, the total count of name should be over 5, so how do i output this record ? because it should just show 5 peoples (Philip, Alex, David, Emily and Tom) if i just use "Top 5" .

Thanks you for your helpin SQL server, you could declare table variable, and insert those names into the table variable, then return from the table variable|||This is effectively a pivot on top of aggregation. That means that it is best done on the client.

With that said, it can be done in SQL. Something like:CREATE TABLE dbo.salary (
name VARCHAR(20) NOT NULL
, salary MONEY NOT NULL
)

INSERT dbo.salary (salary, [name]) VALUES (50, 'Phillip')
INSERT dbo.salary (salary, [name]) VALUES (50, 'Alex')
INSERT dbo.salary (salary, [name]) VALUES (50, 'David')
INSERT dbo.salary (salary, [name]) VALUES (40, 'Emily')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Tom')
INSERT dbo.salary (salary, [name]) VALUES (30, 'May')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Ada')
INSERT dbo.salary (salary, [name]) VALUES (20, 'Queenie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Ellie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Candy')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Hazel')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Stella')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Tanya')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Jacky')

INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sam')
INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sammy')

INSERT dbo.salary (salary, [name]) VALUES ( 3, 'Clyde')

SELECT TOP 5 r.salary, Min(s1.[name]) + Coalesce( ', ' + Min(s2.[name]), '')
+ Coalesce( ', ' + Min(s3.[name]), '') + Coalesce( ', ' + Min(s4.[name]), '')
+ Coalesce( ', ' + Min(s5.[name]), '')
FROM (SELECT TOP 5 salary FROM dbo.salary GROUP BY salary ORDER BY salary DESC) AS r
JOIN (SELECT salary, [name] FROM dbo.salary) AS s1
ON (s1.salary = r.salary)
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s2
ON (s2.salary = r.salary
AND s2.[name] > s1.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s3
ON (s3.salary = r.salary
AND s3.[name] > s2.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s4
ON (s4.salary = r.salary
AND s4.[name] > s3.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s5
ON (s5.salary = r.salary
AND s5.[name] > s4.[name])
GROUP BY r.salary
ORDER BY r.salary DESC-PatP