Removing duplicate Names - 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)

Removing duplicate Names

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-02-20 : 18:10:05

Hi guys,
This may seem like a simple query however Ive been scratching my head and im not too sure on how to do it.


SELECT DISTINCT PAYMENT_POINT, PAYMENT_POINT_NAME
FROM COMMISSION_SUMMARY

The problem the payment_points have different payment point names. for example:
Payment_point Payment_point_name
AA1 Allstar
AA1 NoStar
AA2 Penstar
AA3 Mousestar
BB1 YoStar
BB1 BeStar

Is there anyway I can run the query such that it only returns the first records if there are multiple payment_point_names for the same Payment_point? in the above example choose Allstar, and Bestar for the same payment point, along with the other unique ones?
cheers
Champinco

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-20 : 18:12:44

You can certainly do that - what you mean by "the first records" is tricky. If you mean the first alphabetically then its simple

SELECT PAYMENT_POINT, min(PAYMENT_POINT_NAME)
FROM COMMISSION_SUMMARY
GROUP BY PAYMENT_POINT

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-02-20 : 18:27:49

Yes that works perfectly. however what happens if the payment_point_name is a different datatype such that you can select a min/max etc...
Cheers

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-02-20 : 18:49:29

sorry CAN'T select not CAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-20 : 19:02:32

quote:
what happens if the payment_point_name is a different datatype such that you can select a min/max etc

what datatype is it ? min / max works on numeric and also string

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-02-20 : 19:35:07

yes that is correct. So it would work for both cases then. this solves my problem. I wasnt thinking laterally.
Champinco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources