SQL question

Quote from nitro:

patoo,

Thank you for your code. But here is the problem I am trying to solve: I have a table with columns x,y,z. x can contain many duplicates (say the date without the time part). I want a list of all the x duplicate rows, and their corresponding y and z columns in a table.

Your code only allows me to see the column that is duplicated I believe, something I could achieve as posted in the original code in this thread.
More...

If I am understanding your problem correctly (and I'm not sure I am), try something like this. This should show all rows where there exist multiple rows for the same date x.

select mt1.x, mt1.y, mt1.z
from mytable mt1
where trunc(mt1.x) in (select trunc(mt2.x)
from mytable mt2
group by trunc(mt2.x)
having count(trunc(mt2.x))>1)

I am not logged into a database right now, but this is close to what it would be in Oracle, if not exact. The trunc function removes the timestamp from the date in Oracle. If you are not in Oracle, there should be an equivalent function or way to manipulate the date to remove the timestamp.
 
You must log in or register to reply here.
Back
Top