eliminate duplicates in a table [message #300267] |
Thu, 14 February 2008 16:22 |
NIckman
Messages: 64 Registered: May 2007
|
Member |
|
|
Hi
I have a table which contains duplicates.
I do not want to delete the rows. I want extract only
unique rows.
i have 3 columns
fname,lastname,date
fname lastname date
john don 12feb08
john don 10feb08
john don 1jan07
kris walter 11feb08
kris walter 9feb08
taylor bartin 8jan08
taylor bartin 5feb08
i want data to see like this. i want latest date sorted by last name.
fname lastname date
taylor bartin 5feb08
john don 12feb08
kris walter 11feb08
pl hlep
N.
|
|
|
|
|
|
Re: eliminate duplicates in a table [message #300452 is a reply to message #300280] |
Fri, 15 February 2008 07:51 |
NIckman
Messages: 64 Registered: May 2007
|
Member |
|
|
Hi Abdqul,
thanks for your help.
it eliminated duplicates.but i did not get the latest date
row.
how do i get that.
i used this query.
select * from table_name where rowid in (select min(rowid) from emp group by firstname ); i tried both max and min functions.
it did not picked up the latest date row.
i want latest date row.
pl help
thanks,N.
|
|
|
|
|
Re: eliminate duplicates in a table [message #300547 is a reply to message #300454] |
Fri, 15 February 2008 16:49 |
NIckman
Messages: 64 Registered: May 2007
|
Member |
|
|
here is the sql i ran.
SELECT DISTINCT USR_LASTNAME||','||
USR_FIRSTNAME||','||
USR_LASTSESSION
FROM USR
WHERE USR_LASTSESSION IN
(SELECT DISTINCT MAX(USR_LASTSESSION)
FROM USR
GROUP BY USR_LASTSESSION );
still i do get duplicates for same date.
i tried like this
SELECT USR_LASTNAME||','||
USR_FIRSTNAME||','||
USR_LASTSESSION
FROM USR
WHERE USR_LASTSESSION IN (SELECT MAX(USR_LASTSESSION) FROM USR ORDER BY USR_LASTNAME);
i got ORA-00907: missing right parenthesis.
i see i closed the right paranthesis.
why i am getting this error.
is this query correct to get the lastet date with not duplicates.
help thanks, N.
|
|
|
|
|
|
|
Re: eliminate duplicates in a table [message #303527 is a reply to message #300891] |
Fri, 29 February 2008 13:22 |
NIckman
Messages: 64 Registered: May 2007
|
Member |
|
|
Marc,
Thanks for mail.
i got it with out duplicates. but i sorted by last name which works fine. But when i sorted by latest date it is giving error?.
SELECT LASTNAME||','||
FIRSTNAME||','||
MAX(LASTDATE)
FROM EMP
WHERE LASTNAME
IN ('aaa','cde','bbb','ddd','eee','gfr')
GROUP BY LASTNAME,FIRSTNAME
ORDER BY LASTNAME,FIRSTNAME;
it is working fine. it sorted by lastname and removed the
duplicate recores.
now i need to sort latest date descending.
now i am getting with this code.
SELECT LASTNAME||','||
FIRSTNAME||','||
MAX(LASTDATE)
FROM EMP
WHERE LASTNAME
IN ('aaa','cde','bbb','ddd','eee','gfr')
GROUP BY LASTNAME,FIRSTNAME
ORDER BY LASTDATE DESC;
how to sort by latest date in DESC with out duplicates.
help
thx
N.
|
|
|
|
|