Home » SQL & PL/SQL » SQL & PL/SQL » Get unique rows from table
Get unique rows from table [message #249112] Tue, 03 July 2007 07:36 Go to next message
nissea
Messages: 2
Registered: July 2007
Junior Member
Hi

I have a table with columns 'animal' and 'number' and it looks something like this:

animal number
dog 1
cat 2
goat 2
horse 1
bird 4

I would like to remove rows that have the same number and only get the row where the number first appear. For example in this case I should get:
dog 1
cat 2
bird 4

So if the number appears somtime later in the column it should discard it, if you understand what I mean. Is this possible to do with some SQL query? Appreciate any kind of suggestions!

//Nisse
Re: Get unique rows from table [message #249115 is a reply to message #249112] Tue, 03 July 2007 07:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
nissea wrote on Tue, 03 July 2007 08:36

I would like to remove rows that have the same number and only get the row where the number first appear.


No such thing as where the number "first" appears. There is no "first", "last" or "later" in a relational database.
Re: Get unique rows from table [message #249117 is a reply to message #249112] Tue, 03 July 2007 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In another word: define "first".

Regards
Michel
Re: Get unique rows from table [message #249127 is a reply to message #249117] Tue, 03 July 2007 07:49 Go to previous messageGo to next message
nissea
Messages: 2
Registered: July 2007
Junior Member
In my table I just want to get one row for each number, it doesnt mather if it the first or last row.
icon14.gif  Re: Get unique rows from table [message #249147 is a reply to message #249127] Tue, 03 July 2007 09:07 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Here is you answer:

select col1,col2
from test where rowid in(
SELECT max(rowid) FROM test
     GROUP BY col2
     HAVING count(col2)>=1);


You can use min to retrive the first record of each touples.
then the query should be:
select col1,col2
from test where rowid in(
SELECT min(rowid) FROM test
     GROUP BY col2
     HAVING count(col2)>=1);


Thanks & Regards
Sanka

[Updated on: Tue, 03 July 2007 09:09]

Report message to a moderator

Re: Get unique rows from table [message #249153 is a reply to message #249127] Tue, 03 July 2007 09:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select id, animal
from (select id, animal, 
             row_number() over(partition by id order by rowid) rn
      from mytable)
where rn=1
/

Regards
Michel
Previous Topic: subinventories that have no locators
Next Topic: how to transpose the rows in oracle
Goto Forum:
  


Current Time: Fri Dec 02 18:43:26 CST 2016

Total time taken to generate the page: 0.50066 seconds