dimanche 31 janvier 2016

Get rid of a duplicate row from SQL table

I have a reservation table like this

place      day       name1     floor1    name2     floor2
---------  --------  --------- --------- --------- ---------
Anaheim    Monday    Sean      3rd       Jenny     2nd
Anaheim    Monday    Jenny     2nd       Sean      3rd
Cerritos   Saturday  Dennis    4th       Sean      3rd

As you can see, first and second rows are just duplicate with different order. I want to have a final table like

place      day       name1     floor1    name2     floor2
---------  --------  --------- --------- --------- ---------
Anaheim    Monday    Sean      3rd       Jenny     2nd
Cerritos   Saturday  Dennis    4th       Sean      3rd

I was thinking about something like this

SELECT t1.place, t1.day, t1.name1, t1.floor, t1.name2, t1.floor2
FROM table t1, table t2
WHERE NOT (t1.place = t2.place AND t1.day = t2.day AND
           t1.name1 = t2.name2 AND t1.floor1 = t1.floor2);

But this didn't work :( Maybe my understanding is too short..

How should I approach this?

Aucun commentaire:

Enregistrer un commentaire