Select distinct rows

Question

Select distinct rows

peter.draper

SSC Journeyman

Points: 89
More actions
June 2, 2011 at 1:08 am

#142613

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 15 posts - 1 through 15 (of 15 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Do you use SQL Server 2000 (as indicated by the forum you posted in) or another version? Makes a big difference regarding the solution for the ginven task.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Well my login screen shows Windows Server 2003 enterprise manager, but as far as I am aware there was no server 2003 so this is actually based on server 2000?

Thanks,

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 3

use the window where you typed your query and run the following query

SELECT @@version

The answer you provided is related to the operating system, not the SQL Server version... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Sorry- You can tell I'm new to this whole thing!!

SQL Server 2000 8.00.760

Does this make it harder?

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 5

Something like this?

-- sample data setup

CREATE TABLE #temp

(

part_number CHAR(2) ,

value_ INT ,

Date_ DATETIME

)

INSERT INTO #temp

SELECT 'A1', 432 ,'20110101' UNION ALL

SELECT 'A2', 453 ,'20110101' UNION ALL

SELECT 'A1', 653 ,'20100101' UNION ALL

SELECT 'A4', 875 ,'20090101'

-- actual query

SELECT #temp.*

FROM #temp

INNER JOIN

(

SELECT part_number,MAX(Date_) AS max_date

FROM #temp

GROUP BY part_number

) sub

ON #temp.part_number = sub.part_number AND #temp.Date_ = sub.max_date

ORDER BY #temp.part_number

--cleanup

DROP TABLE #temp

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Thanks- I think I understand most of that.

I know the first bit is a sample data setup, so if I already have put the required rows into a temporary table, can I just use the actual query part, or do I have to do something around a union first?

If i start the 'actual query' part with:

insert into tbl_latestparts

will this just put the rows with unique partnumbers and the latest dates into that table?

Thanks,

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 7

All you need to do is to adjust the table and column names.

The INSERT INTO approach will work as long as the table you're tryingto insert already exist. Otherwise you'd need to use SELECT columns INTO new_table_name FROM....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

That's very helpful, using ther actual naems etc I now have the below:

insert into tbl_weighcalctemp select * from tbl_weigh where partno like 'FGL%' or partno like 'EGL%'

--

-- import into tbl_weighcalc, only using latest if duplicates exist

--

insert into tbl_weighcalc(SELECT tbl_weighcalctemp.*

FROM tbl_weighcalctemp

INNER JOIN

( SELECT partno,MAX(datetime) AS max_date

FROM tbl_weighcalctemp

GROUP BY partno) sub

ON tbl_weighcalctemp.partno = sub.partno AND tbl_weighcalctemp.datetime = sub.max_date

ORDER BY tbl_weighcalctemp.partno)

but it comes up with a couple of errors regarding incorrect syntax, any ideas?

Thanks,

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 9

remove the parenthesis around the SELECT statement after insert into tbl_weighcalc(.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

peter.draper

SSC Journeyman

Points: 89

June 2, 2011 at 5:16 am

#1333006

Works Perfectly!

Many Thanks,

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 11

Glad I could help 😀

Just make sure you'll never have duplicate date values per datetime column. A unique check constraint would help a lot here.

You might also rethink the column names: datetime is a data type (such is "value"). A column name should describe the business related context, not just duplicate the data type. Something like calc_value and calc_datetime.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

What do you mean regarding duplicate date values per column?

Is that with regard to a row with the same partno and same datetime entry?

Peter

LutzM SSC Guru Points: 107049 More actions · Answer 13

Using your original sample:

A1 432 1/1/11

A2 453 1/1/11

A1 555 1/1/11

A1 653 1/1/10

A4 875 1/1/09

Both rows have the same part_no and the same calc_date, but different calc_values. Which one would you like to keep?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

I understand, in the real data it is date and time, so there should never be that situation 🙂

Peter