How to get a random name for each distinct ID

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

Transact-SQL (2000)

How to get a random name for each distinct ID

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-12-28 : 14:06:49

Hi All,

I have a Table Product with the following fileds.
ProdID (varchar type),Name (varchar type)

Each ProdID may have multiple names (not all of them but some products might have multiple name). For example look at following records.

ProdID (varchar type),Name (varchar type)
123,Pepsi 20 OZ
123,Pepsi 20 OZ bottle
456, Coke 1 L
456, Coke 1 Litre Bottle

Now, I want to get each productid with one respective name. I want to pick any one name randomly for each product. So that I should have one name per productid.
For above example my select statement should somehow get any one randome name per product. The result may be

ProdID (varchar type),Name (varchar type)
123,Pepsi 20 OZ bottle
456, Coke 1 L

How can I achieve this?

Lokking for a quick help. Thanks a million in advance.

Kristen
Test

22859 Posts

Posted - 2006-12-28 : 14:23:45

SELECT ProdID, MAX(Name)
FROM MyTable
GROUP BY ProdID

??

You might want to pick the MIN or possibly the one with the LONGEST length (MIN and MAX may be flawed by spurious punctuation)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-29 : 08:41:21

I hope that this SELECT is a tool you are using to clean your data -- you really should have a table of Products with a strong primary key that disallows duplicates with a single, definitive Name column for each.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources