Distinct records?? - 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)

Distinct records??

Author

Topic

mig1980
Starting Member

1 Post

Posted - 2012-03-01 : 18:43:27

Good day. I have a query that I am building which joins 5 tables. The query is to pull distinct records based on two columns and pull the rest of the columns in if available.

I am new to SQL and am having trouble pulling distinct records for only two of the columns. I wrote the query and it pulls distinct records based on all columns. The query holds sensitive information so I won't be able to post it but can someone guide me to how I can do this?

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-01 : 21:08:35

One way to do it is using the row_number function. In my example, col1 and col2 are the distinct columns and col3, col4, col5 are the others. I arbitrarily chose to order by col3, col4, col5 and pick the first one if there are dups. You can tailor to your needs.

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY col1,col2 ORDER BY col3,col4,col5) AS RN
	FROM
		YourTable
)
SELECT
	col1,col2,col3,col4,col5
FROM
	cte
WHERE
	RN = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources