OraFAQ Forum: SQL & PL/SQL » eliminate duplicates in a table

Home » SQL & PL/SQL » SQL & PL/SQL » eliminate duplicates in a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: eliminate duplicates in a table [message #300275 is a reply to message #300267]

Thu, 14 February 2008 20:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try a MAX() function

Report message to a moderator

Re: eliminate duplicates in a table [message #300280 is a reply to message #300267]

Thu, 14 February 2008 21:02

Abdul Qadeer
Messages: 6
Registered: August 2007
Location: Peshawar

Junior Member

Try this one if it helps to solve your problem.
--select * from table_name where rowid in (select min(rowid) from emp group by firstname );

Report message to a moderator

Re: eliminate duplicates in a table [message #300295 is a reply to message #300267]

Thu, 14 February 2008 23:03

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have also a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel

[Updated on: Thu, 14 February 2008 23:04]

Report message to a moderator

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.

Report message to a moderator

Re: eliminate duplicates in a table [message #300454 is a reply to message #300452]

Fri, 15 February 2008 07:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle doesn't read your mind.

If you want to get records with the latest date, you have to instruct Oracle to do that.

Now, how would you code "latest date"?

Report message to a moderator

Re: eliminate duplicates in a table [message #300455 is a reply to message #300452]

Fri, 15 February 2008 07:57

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

That's because ROWID serves no purpose to the solution. Not sure why that was suggested at all.

You simply need to do a simple SELECT using the MAX function as Ross has suggested.

Report message to a moderator

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.

Report message to a moderator

Re: eliminate duplicates in a table [message #300556 is a reply to message #300547]

Sat, 16 February 2008 00:10

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

you don't need to do that DISTINCT's and SUBQUERY's.just use MAX() as suggested before.

And format your code using CODE tags

regards,

Report message to a moderator

Re: eliminate duplicates in a table [message #300890 is a reply to message #300547]

Mon, 18 February 2008 08:51

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

NIckman wrote on Fri, 15 February 2008 17:49

WHERE USR_LASTSESSION IN (SELECT MAX(USR_LASTSESSION) FROM USR ORDER BY USR_LASTNAME);

ORDER BY has no meaning in this context.

Report message to a moderator

Re: eliminate duplicates in a table [message #300891 is a reply to message #300890]

Mon, 18 February 2008 08:57

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

NIckman: please have a look at Oracle's documentation, in particular the MAX()-function and the GROUP BY clause

It's all explained there, even with examples

[Updated on: Mon, 18 February 2008 09:00]

Report message to a moderator

Re: eliminate duplicates in a table [message #300958 is a reply to message #300891]

Mon, 18 February 2008 18:14

NIckman
Messages: 64
Registered: May 2007

Member

Thanks all of you.
i got it.
N.

Report message to a moderator

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.

Report message to a moderator