remove duplicate entries - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

remove duplicate entries

Author

Topic

tyrantbliss
Starting Member

3 Posts

Posted - 2011-08-09 : 01:26:19

how to create a query from this table
SAMPLE DATA
Driver Destination TransportType Date

carl new york car 08-08-2011
carl new jersey motorcycle 08-08-2011
carl new york motorcycle 08-08-2011
ray los angeles car 08-12-2011
ray new jersey car 08-08-2011
ray miami motorcycle 08-08-2011

SAMPLE OUTPUT
Driver Destination TransportType Date

carl new york car 08-08-2011
ray los angeles car 08-12-2011

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-09 : 01:40:45

Tyrant, what are you considering to be duplicate entries?

tyrantbliss
Starting Member

3 Posts

Posted - 2011-08-09 : 01:44:43

quote:
Originally posted by flamblaster

Tyrant, what are you considering to be duplicate entries?

i would like only unique drivers to be listed. so if there are multiple names of driver only one name will be outputed

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-09 : 01:55:55

Ah, ok...misunderstood. Although your sample output is not duplicated. If you want to only see the unique drivers, you could do this:

Select Driver
From YourTable
Group by Driver

tyrantbliss
Starting Member

3 Posts

Posted - 2011-08-09 : 02:22:27

quote:
Originally posted by flamblaster

Ah, ok...misunderstood. Although your sample output is not duplicated. If you want to only see the unique drivers, you could do this:

Select Driver
From YourTable
Group by Driver

i have used the query SELECT driver, destination, transporttype, date FROM DRIVER_TBL GROUP BY driver, destination, transporttype, date and came up with the result table that i shown
Driver || Destination || TransportType || Date

carl || new york || car || 08-08-2011
carl || new jersey || motorcycle || 08-08-2011
carl || new york || motorcycle || 08-08-2011
ray || los angeles || car || 08-12-2011
ray || new jersey || car || 08-08-2011
ray || miami || motorcycle || 08-08-2011

i would like a result like

Driver Destination TransportType Date

carl new york car 08-08-2011
ray los angeles car 08-12-2011

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-09 : 02:23:58

Nothing is duplicated though. The only way you'll get those 2 results would be to put those criteria in a Where clause which wouldn't seem very useful.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-08-09 : 02:30:07

try by using row_number function.


DECLARE  @table TABLE(Driver VARCHAR(32), Destination VARCHAR(32), TransportType VARCHAR(32), Date DATE)
INSERT INTO @Table SELECT 'carl', 'new york', 'car', '08-08-2011'
INSERT INTO @Table SELECT 'carl', 'new jersey', 'motorcycle', '08-08-2011'
INSERT INTO @Table SELECT 'carl', 'new york', 'motorcycle', '08-08-2011'
INSERT INTO @Table SELECT 'ray', 'los angeles', 'car', '08-12-2011'
INSERT INTO @Table SELECT 'ray', 'new jersey', 'car', '08-08-2011'
INSERT INTO @Table SELECT 'ray', 'miami', 'motorcycle', '08-08-2011'

SELECT Driver,Destination,TransportType,Date FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Driver ORDER BY Driver,TransportType,Date)AS Rid, *  FROM @table)s WHERE Rid = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources