Home » SQL & PL/SQL » SQL & PL/SQL » eliminate duplicates in a table
eliminate duplicates in a table [message #300267] Thu, 14 February 2008 16:22 Go to next message
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 #300275 is a reply to message #300267] Thu, 14 February 2008 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try a MAX() function
Re: eliminate duplicates in a table [message #300280 is a reply to message #300267] Thu, 14 February 2008 21:02 Go to previous messageGo to next message
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 );
Re: eliminate duplicates in a table [message #300295 is a reply to message #300267] Thu, 14 February 2008 23:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #300454 is a reply to message #300452] Fri, 15 February 2008 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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"?
Re: eliminate duplicates in a table [message #300455 is a reply to message #300452] Fri, 15 February 2008 07:57 Go to previous messageGo to next message
joy_division
Messages: 4642
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.
Re: eliminate duplicates in a table [message #300547 is a reply to message #300454] Fri, 15 February 2008 16:49 Go to previous messageGo to next message
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 #300556 is a reply to message #300547] Sat, 16 February 2008 00:10 Go to previous messageGo to next message
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,
Re: eliminate duplicates in a table [message #300890 is a reply to message #300547] Mon, 18 February 2008 08:51 Go to previous messageGo to next message
joy_division
Messages: 4642
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.
Re: eliminate duplicates in a table [message #300891 is a reply to message #300890] Mon, 18 February 2008 08:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Thanks all of you.
i got it.
N.
Re: eliminate duplicates in a table [message #303527 is a reply to message #300891] Fri, 29 February 2008 13:22 Go to previous messageGo to next message
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.


Re: eliminate duplicates in a table [message #303536 is a reply to message #303527] Fri, 29 February 2008 13:39 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You can only ORDER BY a selected column name or number. You are not selecting LASTDATE, only a concatenation of MAX(LASTDATE).

You have to use
order by max(lastdate)

[Updated on: Fri, 29 February 2008 13:41]

Report message to a moderator

Re: eliminate duplicates in a table [message #303557 is a reply to message #303536] Fri, 29 February 2008 14:31 Go to previous message
NIckman
Messages: 64
Registered: May 2007
Member
Thank you.
i got it.
N.
Previous Topic: pl/sql program for table updation
Next Topic: small problem with this query
Goto Forum:
  


Current Time: Wed Dec 07 16:31:44 CST 2016

Total time taken to generate the page: 0.05515 seconds