dinstinct with multiple columns

[JazzMaan]

i need distinct part and rest of the fields,
how do i do it?

select part, desc, qty... from partstable

i cant do

select distinct part, desc, qty... from partstable
as I only need one of records in case the part# is repeated

 

[RiverGuy]

Well, if you have two or more of the same part#, you need to be able to let SQL Server know which record you want. So how do you plan on doing that? Do you have a LastUpdated column which is unique to each part#?

 

[markros]

In addition to RiverGuy's reply check

Including an Aggregated Column's Related Values

 

[JazzMaan]

What if i dont have a last updated column.

Seems like this is a case of bad data.

How can i write a query to randomly pick one of many records for a given sku?

 

[jbenson001]

TOP 1

 

[markros]

Pick up any field and do a ROW_NUMBER() solution OVER (order by AnyField you pick). It would not be random, for random you may want

ROW_NUMBER() OVER (order by NewID())

but I never tried this with ROW_NUMBER.

This, of course, will work for SQL Server 2005 and up and well documented in the blog I pointed you earlier.

For SQL Server 2000 you may want to use the 5th solution idea - pick any other field.

 

[markros]

BTW, do you have an ID field in your table? Can you just get the record with maximum or minimum ID ?

 

[JazzMaan]

I am sorry, could you provide the query
blog is working with 2 tables, In my case I only have one table.

 

[markros]

select * from (select part, desc, qty, row_number() over (partition by Part order by Qty Desc) as RowNum from partstable) X where RowNum = 1

I picked Qty field - you c an try alternatively

partition by Part order by NewID() - I never tried it, so it may not work.

Tired a bit now, besides, I've been in a car accident - may be this query will need a bit of tuning...