duplicate rows [message #257817] |
Thu, 09 August 2007 04:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
eur6lts
Messages: 4 Registered: August 2007
|
Junior Member |
|
|
hi,
i have duplicate rows in the database and in the worksheet.
the rows are distinguished by one column.
how can i show only one row of each kind?
thanks
|
|
|
Re: duplicate rows [message #257888 is a reply to message #257817] |
Thu, 09 August 2007 07:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If those rows can be distinguished by one column, they are NOT duplicates.
You might try something like this:
SELECT MAX(distinguishing_column), name, address, blabla
FROM this_table
GROUP BY name, address, blabla;
MAX function (or MIN or similar) will force selecting only one of those "distinguishing" columns (you choose which one), but you'll have to GROUP BY all other columns.
Or, you might try
SELECT t.distinguishing_column, t.name, t.address, t.blabla
FROM this_table t
WHERE distinguishing_column = (SELECT MAX(t1.distinguishing_column)
FROM this_table t1
WHERE t1.primary_key_col = t.primary_key_col
);
This would require your table to enable joining on primary key column - or columns; in your case, you might need to join ALL columns except 'distinguishing_column'.
|
|
|