Filtering out Duplicate Records

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

Old Forums

CLOSED - General SQL Server

Filtering out Duplicate Records

Author

Topic

phconrad
Starting Member

12 Posts

Posted - 2003-05-13 : 11:48:12

I'm trying to get together a result set that will not contain duplicate data. The problem is the table I'm working with doesn't have a "true" primary key.

Here's a sample of the data:

ship_no status date cktime city st
---------- ------- ------------- ------ -------------------- ------
408859 E 2003-05-12 20:49 L.A. CA
408859 E 2003-05-12 10:00 L.A. CA
408859 E 2003-05-12 10:00 L.A. CA
408859 E 2003-05-12 10:00 L.A. CA
408859 E 2003-05-12 07:30 L.A. CA
408859 E 2003-05-11 23:15 KOKOMO IN
408859 E 2003-05-10 11:30 KOKOMO IN
408859 E 2003-05-10 11:30 KOKOMO IN
408859 E 2003-05-07 05:37 KOKOMO IN
408859 E 2003-05-07 05:37 KOKOMO IN
408859 P 2003-05-07 12:05 CINCINNATI OH

Some of the records have the same date, time, city and state which is what I'm trying to filter.

thanks,
Phil

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 11:52:30

Are you looking for:

SELECT DISTINCT ship_no, status, date, cktime, city, st FROM yourTable

Brett

8-)

phconrad
Starting Member

12 Posts

Posted - 2003-05-13 : 11:56:53

Yes! That's exactly what I needed. I didn't realize I could use SELECT DISTINCT like that. Thank you.

phil

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-13 : 12:14:41

Geez Brett,

I wish someone would toss me a softball like that!!! J/K

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 12:27:16

Watch your heels Chad....

PS I didn't think that was going to be the answer...it's usually:

"I need distinct values for the first three columns, but not the last 2"

I hate Access (see expr1:first(col1) or expr1:last(col1))

And anyway, thinking about it now, how does SQL Server translate that? Is it done in JET?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources