Select Top Distinct

Question

Select Top Distinct

peter.draper

SSC Journeyman

Points: 89
More actions
June 1, 2011 at 7:20 am

#143932

Hi,
I'm relatively new to the sql language and am currently stuggling with a problem which I could do with some help with, and will try and describe below.
I have a large table, with part number, value, and datetime columns.
I am then extracting rows from this table which meet a criteria placed on the partnumber, and putting them into a secondary table. This is working fine by using standard 'select' and 'where' statements, but I need to look at error proofing.
I only want to have rows with a unique partnumber copied from the main table.
So if the main table looks like this:
A1 432 1/1/11
A2 453 1/1/11
A1 653 1/1/10
A4 875 1/1/09
I only want part numbers that begin with A, and only 1 row for each partnumber. Where there are multiple rows with the same partnumber, I want the one with the most recent datetime.
Does anyone know how to do this?
Many Thanks,
Peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

SELECT * FROM (

SELECT *, ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY Date DESC) AS Rid FROM table where part like 'A%') dta WHERE Rid = 1

peter.draper SSC Journeyman Points: 89 More actions · Answer 2

Thanks for the quick reply- what is the first 'select * from (' for?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can't put the where Rid = 1 in the same scope as the ROW_NUMBER function.

So you have to wrap the select as a derived table (CTE would be fine too) and then apply the filter.

peter.draper SSC Journeyman Points: 89 More actions · Answer 4

Thanks, I've just given this a try and got an error that row_number() is not supported.

I had a look and have found that I actually have server 2000, any ideas?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

peter.draper (6/1/2011)
Thanks, I've just given this a try and got an error that row_number() is not supported.
I had a look and have found that I actually have server 2000, any ideas?

Yes post your question in sql 7, 2000 forum next time :hehe:.

I don't have a link handy nor sample script... I'll see if someone else can get in here and help you out.