MAX of two columns with common field

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)

MAX of two columns with common field

Author

Topic

JimmyFo
Starting Member

11 Posts

Posted - 2009-09-18 : 14:47:15

Hi, I've got a table where I am trying to find the maximum of two columns with only one occurrence of a particular field. As an example, I am getting this back:

c1      c2     c3
-----------------
1	151	2
2	152	2
3	189	2
4	189	1

I am trying to reduce it to this:

c1      c2     c3
-----------------
1	151	2
2	152	2
4	189	1

The query I am using is this:

		SELECT DISTINCT
			MAX(T.TypesettingID),
			T.WorksheetID,
			MAX(T.Revision)
		FROM
			tblTypesetting AS T
		GROUP BY
			T.TypesettingID,
			T.WorksheetID

Any ideas? Thanks -

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-18 : 15:03:27

your columns in the query doesn't match with ur columns in the sample data..so i dont know for sure...but maybe this..


SELECT * FROM 
(
SELECT 
			row_number () over(partition by T.WorksheetID order by T.TypesettingID desc,T.Revision desc) as rn,
			T.TypesettingID,
			T.WorksheetID,
			T.Revision
		FROM
			tblTypesetting AS T
		
) R
WHERE R.rn = 1

JimmyFo
Starting Member

11 Posts

Posted - 2009-09-18 : 15:06:17

Awesome! I've never seen "row_number" before, I need to look into that - the columns didn't match since I just was copying data over, but this solves it!

Thanks -

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-19 : 02:18:59

quote:
Originally posted by JimmyFo

Awesome! I've never seen "row_number" before, I need to look into that - the columns didn't match since I just was copying data over, but this solves it!

Thanks -

Morte about Row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources