Using Min to return min value for each profile

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)

Using Min to return min value for each profile

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2007-08-26 : 08:04:27

Hi all,
i have this table called Profile

RecID ModID ProfileName
-------------------------------------------------------
1 5 My Account Management
2 5 My Account Management
3 5 My Account Management
4 7 My Profile
5 7 My Profile
6 7 My Profile

I would like to return the min value for RecID for each ProfileName.

RecID ModID ProfileName
---------------------------------
1 5 My Account Management
4 7 My Profile

I am not very good in SQL statement , please help thanks.

kt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-26 : 08:09:57

[code]SELECT p.RecID, p.ModID, p.ProfileName
FROM Profile p
INNER JOIN
(
SELECT RecID = MIN(RecID), ProfileName
FROM Profile
GROUP BY ProfileName
) m
ON p.RecID = m.RecID[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-26 : 10:38:49

Or the SQL Server 2005 approach. If you have many records this may be faster.
Compare them both in SSMS and SQL Profiler.


SELECT RecID, ModID, ProfileName
FROM (
SELECT RecID, ModID, ProfileName, ROW_NUMBER() OVER (PARTITION BY ProfileName ORDER BY RecID) AS RowID
FROM   Profile
) AS d
WHERE RowID = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources