Select all fields with max(Col) but ignore dup

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 2008 Forums

Transact-SQL (2008)

Select all fields with max(Col) but ignore dup

Author

Topic

Inquisitor
Starting Member

4 Posts

Posted - 2009-10-21 : 13:13:24

Hello All,

I am teaching my self SQL. I have a table with data that I have created with an Incremental ID counter. I want to select all fields from my table displaying only the highest number in the Incremental ID. Meaning filter out all rows that are less then the highest Incremental ID number.

I have used the Group By.

SELECT [Log_id]
,[X_SP]
,[Y_SP]
,[Z]
,[Date]
,max([Inc_ID]) as IDEN --This is the Incremental ID counter

FROM [ENV].[dbo].[Proposed_Samples]
group by Log_id
,IEPA_id
,X_SP
,Y_SP
,[Z]
,Date

My problem is when the data is displayed I get all records including the full range of the incremental ID counters. I do not get only the highest rows of the incremental ID counter.

Thank you in advance for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 13:19:34

[code]SELECT TOP 1 [Log_id]
,[X_SP]
,[Y_SP]
,[Z]
,[Date]
,[Inc_ID] as IDEN --This is the Incremental ID counter

FROM [ENV].[dbo].[Proposed_Samples]
ORDER BY [Inc_ID] DESC
[/code]

Inquisitor
Starting Member

4 Posts

Posted - 2009-10-21 : 13:54:12

Visakh16,

The example you gave me lists only 1 record out of my 46 records in my table. My table has duplicate entries of a point. I created the counter to filter out for only the highest result of a duplicated point. Some points will have only 1 record. Other records will be a duplicate and have an incremental ID counter of 2, 3 or greater. I want to list all the points with the highest incremental ID counter.

Thank you for your suggestion though.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-21 : 14:03:59

There are several ways to do this, here is one (I wasn't 100% sure about the paritioning and such, but it might work):

SELECT 
	*
FROM
	(
		SELECT 
			Log_id
			,X_SP
			,Y_SP
			,Z
			,Date
			,ROW_NUMBER() OVER 
			(
					PARTITION BY
						Log_id
						,IEPA_id
						,X_SP
						,Y_SP
						,Z
						,Date
					ORDER BY 
						Inc_ID DESC
			) AS RowNum
		FROM 
			ENV.dbo.Proposed_Samples
	) AS T
WHERE 
	RowNum = 1

You can also do this with a CROSS APPLY or joining to a derived table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources