Getting records from table where some attribute is unique

Question

I have a table Person (id, name, city).

I want to return the records of persons with only one person from each city (city should be unique).

For example, if there are 10 records out of which 3 have city = 'Mumbai', other 3 have city = 'New York' and remaining 4 have city = 'Chicago', then I want to return 3 records (one with each city).

I tried various things including DISTINCT, ROWNUM, but not getting the desired result.

Can anyone please tell me how can this be achieved?

Answer 1

Try this:-

  select city,id, name
  from Person
  group by city
  order by city

Answer 2

If you mean you want to return those cities which only have one person listed...

Select Max(ID), max(name), City, count(city)
From Person
Group by City
Having count(City) = 1

If you mean you want to return a distinct list of cities...

SELECT city
FROM Person
GROUP BY city

If you mean you want to return all the persons in a city but only list the city once... what results do you want to see? if you need the persons name/Id this is impossible as your records have 3 users in 2 different cities and 4 usrs in the last city. so what "person" do you want to see in the results... so in Mumbai there is A, B, C... what result would you expect to see for that city?

Answer 3

Could this be what you are looking for? A single record with a count of how many names for each city??

select city,count(*) as COUNT
from Person
group by city
order by city

Answer 4

You want one row per city no matter which one?

select city,id, name
from
  (select city, id, name,
      row_number() over (partition by city order by id) as rn 
   from Person
  ) dt
where rn = 1

This returns the row with the lowest id per city.