GROUP BY!

SELECT MIN(Salary) FROM (
SELECT TOP 10 Salary FROM tblEmp ORDER BY Salary DESC) AS X
As such the resultset of the above query will only have the record of
the Salary column. I want the record of another column named EmpName as
well in the resultset. So where do I accomodate the 'GROUP BY EmpName'
clause in the above query to get the EmpName in the resultset?
Thanks,
ArpanOne way to accomplish this:
SELECT
Salary,
EmpName
FROM tblEmp
WHERE Salary =
(
SELECT
MIN(Salary)
FROM
(
SELECT TOP 10
Salary
FROM tblEmp
ORDER BY
Salary DESC
) AS X
)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1130343229.752921.153920@.o13g2000cwo.googlegroups.com...
> SELECT MIN(Salary) FROM (
> SELECT TOP 10 Salary FROM tblEmp ORDER BY Salary DESC) AS X
> As such the resultset of the above query will only have the record of
> the Salary column. I want the record of another column named EmpName as
> well in the resultset. So where do I accomodate the 'GROUP BY EmpName'
> clause in the above query to get the EmpName in the resultset?
> Thanks,
> Arpan
>|||Thanks, Adam.
Regards,
Arpan

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

group by Aggregate

I have the following table.
id Deptid Staffid Salary
1 1 100 85000
2 1 101 75000
3 2 201 90000
3 2 202 35000
I like to find the minumim salary for each dept and its corresponding
staffid value.
Thus.. the result should be...
Deptid Salary Staffid
1 75000 101
2 35000 202
select deptid,min(salary) from test
group by deptid
will yield the correct result less the staffid. How do I capture the
staffid for the min(salary)?
create table test (id int,deptid int, staffid int,salary int)
insert into test values (1,1,100,85000)
insert into test values (2,1,101,75000)
insert into test values (3,2,201,90000)
insert into test values (3,2,202,35000)
Many thanks.
ShahriarHi
untested
SELECT * FROM Table WHERE Salary =(SELECT MIN(Salary) FROM Table T
WHERE T.Deptid =Table.Deptid AND T.id <=Table.id)
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:HphNf.10696$XE6.4888@.trnddc07...
> I have the following table.
> id Deptid Staffid Salary
> 1 1 100 85000
> 2 1 101 75000
> 3 2 201 90000
> 3 2 202 35000
> I like to find the minumim salary for each dept and its corresponding
> staffid value.
> Thus.. the result should be...
> Deptid Salary Staffid
> 1 75000 101
> 2 35000 202
> select deptid,min(salary) from test
> group by deptid
> will yield the correct result less the staffid. How do I capture the
> staffid for the min(salary)?
> create table test (id int,deptid int, staffid int,salary int)
> insert into test values (1,1,100,85000)
> insert into test values (2,1,101,75000)
> insert into test values (3,2,201,90000)
> insert into test values (3,2,202,35000)
>
> Many thanks.
> Shahriar
>
>|||SELECT a.DeptID,a.Salary,a.Staffid
FROM test a
INNER JOIN(
SELECT MIN(Salary),DeptID
FROM test
GROUP BY DeptID) b(Salary,DeptID) ON a.Salary=b.Salary
AND a.DeptID=b.DeptID|||Shahriar wrote:
> I have the following table.
> id Deptid Staffid Salary
> 1 1 100 85000
> 2 1 101 75000
> 3 2 201 90000
> 3 2 202 35000
> I like to find the minumim salary for each dept and its corresponding
> staffid value.
> Thus.. the result should be...
> Deptid Salary Staffid
> 1 75000 101
> 2 35000 202
> select deptid,min(salary) from test
> group by deptid
> will yield the correct result less the staffid. How do I capture the
> staffid for the min(salary)?
> create table test (id int,deptid int, staffid int,salary int)
> insert into test values (1,1,100,85000)
> insert into test values (2,1,101,75000)
> insert into test values (3,2,201,90000)
> insert into test values (3,2,202,35000)
>
> Many thanks.
> Shahriar
What are the keys? What if there is more than one person with the same
minimum salary for one department?
Try:
SELECT id, deptid, staffid, salary
FROM test AS T
WHERE salary =
(SELECT MIN(salary)
FROM test
WHERE deptid = T.deptid);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--