Selecting Multiple DISTINCT columns

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

Selecting Multiple DISTINCT columns

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2012-03-15 : 14:14:54

Hi All - I have the following table. The PRICE_CODE is the unique identifier, but is duplicated beacause it has multiple Dates, and descriptions:

[CODE]
PRICE_CODE | DATE | DESCRIPTION

1003ABCDEF 01/01/11 Plan1
1003ABCDEF 01/03/12 Plan2
1003ABCDEF 01/31/12 Plan3
[/CODE]

I need run a query that outputs the distinct Price_Code and other fields based on the latest Date.

Output would be: [CODE]1003ABCDEF 01/31/12 Plan3
[/CODE]
I've figured out how to output the results based on the latest "Date" however it still outputs multiple records because the same price_code has multiple descriptions. How can I make the description distinct?

[CODE]
SELECT PRICE_CODE, DESCRIPTION, DATE=MAX(DATE) FROM TABLE1
GROUP BY PRICE_CODE, DESCRIPTION
[/CODE]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:21:43

SELECT * FROM yourTable o WHERE EXISTS (
SELECT * FROM yourTable i WHERE o.PRICE_CODE = i.PRICE_CODE
GROUP BY i.PRICE_CODE
HAVING o.[DATE] = MAX(i.[DATE])
)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-15 : 14:34:26

Well PRICE_CODE is NOT the unique identifier. :)

You can do as Brett suggested. Additionally, you can use the ROW_NUMBER() function, an OUTTER APPLY, a join to derived table, etc.. Lots of options.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:23:39

using cross apply


SELECT t.*
FROM table t
CROSS APPLY (SELECT MAX([date]) AS latest
             FROM table
             WHERE PRICE_CODE = t.PRICE_CODE 
            )t1
WHERE t1.latest = t.[Date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:39:04

Wait..I just got it

"Multiple DISTINCT Columns"

You funny

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources