CODEWITHSUNDEEP
sqloracle-database
Rolando
Rolando

Reputation: 62664

Is there a way to query for distinct row records in oracle?

I have the following table in Oracle where all fields are the same except for "Name":

Name, Age, Nickname, FavSport
A, 5, Jordan, Hockey
A, 6, Jordan, Hockey
B, 5, Jordan, Hockey
B, 6, Jordan, Hockey

Is there a way to execute a query such that I get "distinct records" based on all other fields besides a single field?

i.e. Sample Output

A, 5, Jordan, Hockey
A, 6, Jordan, Hockey

Or is there even a better way where I can get:

A-B, 5, Jordan, Hockey
A-B, 6, Jordan, Hockey

Upvotes: 0

Views: 696

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23381

Yes, there is:

select LISTAGG(name, '-'),
       Age, Nickname, FavSport
  from yourTable
group by Age, Nickname, FavSport

The above query will give you:

A-B, 5, Jordan, Hockey
A-B, 6, Jordan, Hockey

To solve it on the first output a simple where clause would do:

select name,
       Age, Nickname, FavSport
  from yourTable
 Where name = 'A'

Upvotes: 1

Related Questions