MIN function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MIN function

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-09-22 : 08:04:10

Hi guys,

I have a table below:
UserID, Department, Salary
1, HR, 100
2, HR, 255
3, HR, 300
4, IT, 355
5, IT, 400
6, IT, 599

Question: How shall i produce a query to select the lowest salary grouped by department? Something like below:

UserID, Department, Salary
1, HR, 100
4, IT, 355

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 08:08:16

[code]SELECT UserID, Department, Salary
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary) AS Seq,*
FROM YourTable)t
WHERE t.Seq=1[/code]

or

[code]SELECT t.*
FROM YourTable t
INNER JOIN (SELECT Department,MIN(Salary) AS MinSal
FROM yourTable
GROUP BY Department) tmp
ON tmp.Department=t.Department
AND tmp.MinSal=t.Salary[/code]

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-09-22 : 20:47:26

Thanks visakh16. Both methods worked fine for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources