I have a table that looks something like this
| city | num | something |
|---|---|---|
| LA | 1233 | av |
| NYC | 1233 | ad |
| ATL | 1233 | ac |
| SF | 426 | ah |
| Hollywood | 426 | ap |
and I want something like this where we just pick one row of all the distinct nums but only one.
| city | num | something |
|---|---|---|
| LA | 1233 | av |
| SF | 426 | ah |
What query method would be most effective at getting the second table?
I chose LA 1233 av at random. I do not care what specific instance of 1233 that I get, just that I only get.
CodePudding user response：
here is one way :
select * from (
select * , row_number() over (partition by num order by random()) as rn
from tablename
) t where rn = 1
which return random select each time , but if you don't care use a column like city in your order by clause.