return full max record - 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

General SQL Server Forums

New to SQL Server Programming

return full max record

Author

Topic

petebob796
Starting Member

35 Posts

Posted - 2008-03-24 : 13:26:35

Is there a t-sql shortcut/ quick way to return the full max record.

So basically if I had:
prog_area glh description
HS 200 health and social care
EN 300 engineering
HS 400 health care
EN 250 engineering and construction

so grouping by prog_area and taking max glh would return appropriate descriptions like:
HS 400 health care
EN 300 engineering

This is quite a simple example and I would be using it on much larger datasets. I know how to do it by doing a join to itself with max glh but it seems to me there should be an easier way to return the appropriate full record set. Hopefully somethin fast in t-sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-24 : 14:09:33

If its SQL 2005 you can also do it using ROW_NUMBER() function

SELECT t.prog_area, 
       t.glh, 
       t.description
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY prog_area ORDER BY glh DESC) AS RowNo,
prog_area, glh, description
FROM Table
)t
WHERE t.RowNo=1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 14:10:34

I think will work for you (untested):

SELECT *
FROM MyTable AS A
INNER JOIN
	(
		SELECT prog_area, MAX(glh) AS glh
		FROM MyTable
		GROUP BY prog_area
	) AS B
	ON A.prog_area = B.prog_area
	AND A.glh = B.glh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources