193

I have a table on pgsql with names (having more than 1 mio. rows), but I have also many duplicates. I select 3 fields: id, name, metadata.

I want to select them randomly with ORDER BY RANDOM() and LIMIT 1000, so I do this is many steps to save some memory in my PHP script.

But how can I do that so it only gives me a list having no duplicates in names.

For example [1,"Michael Fox","2003-03-03,34,M,4545"] will be returned but not [2,"Michael Fox","1989-02-23,M,5633"]. The name field is the most important and must be unique in the list everytime I do the select and it must be random.

I tried with GROUP BY name, bu then it expects me to have id and metadata in the GROUP BY as well or in a aggragate function, but I dont want to have them somehow filtered.

Anyone knows how to fetch many columns but do only a distinct on one column?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
NovumCoder
  • 4,349
  • 9
  • 43
  • 58

4 Answers4

349

To do a distinct on only one (or n) column(s):

select distinct on (name)
    name, col1, col2
from names

This will return any of the rows containing the name. If you want to control which of the rows will be returned you need to order:

select distinct on (name)
    name, col1, col2
from names
order by name, col1

Will return the first row when ordered by col1.

distinct on:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Community
  • 1
  • 1
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Good catch on ordering. I didn't include it because they mentioned wanting a random ordering, but it's important to mention anyway. – Craig Ringer Jun 04 '13 at 12:49
  • Is the `order by name` required? Would it produce a different result with `order by col1`? – Elliot Chance Aug 05 '15 at 02:05
  • 1
    @elliot yes `name` is necessary. Check `distinct on` at the manual. – Clodoaldo Neto Aug 05 '15 at 11:27
  • 1
    I wish the TSQL team could provide such a sensible way of doing this. – JTW Nov 08 '15 at 01:43
  • Please add the appropriate postgresql [reference](https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT) – Ogaga Uzoh Nov 30 '17 at 06:21
  • 1
    Ugh me too, this has been plaguing me for weeks now. I want distinct on one column but order by something else that isn't the distinct column. Why is it so hard in Postgres? A subquery is way too slow as it will evaluate the entire thing before returning the outer order by. Frustrating beyond belief! – Kevin Parker Mar 19 '20 at 17:34
  • @KevinParker https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by? – jian Sep 25 '21 at 11:45
27

Anyone knows how to fetch many columns but do only a distinct on one column?

You want the DISTINCT ON clause.

You didn't provide sample data or a complete query so I don't have anything to show you. You want to write something like:

SELECT DISTINCT ON (name) fields, id, name, metadata FROM the_table;

This will return an unpredictable (but not "random") set of rows. If you want to make it predictable add an ORDER BY per Clodaldo's answer. If you want to make it truly random, you'll want to ORDER BY random().

iainn
  • 16,826
  • 9
  • 33
  • 40
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 2
    Just note with this DISTINCT ON clause, you can only ORDER BY the same thing + more. So if you say DISTINCT ON (name) you must ORDER BY name then whatever else you want. Hardly ideal. – Kevin Parker Mar 19 '20 at 17:37
  • Kevin, you can just use a CTE or subquery-in-FROM and ORDER BY in the outer query – Craig Ringer Mar 22 '20 at 00:11
  • 1
    Yes, and watch the performance go... The entire possible results from the index space will be searched. It turns what *could* be a 10-20ms query with the right index into a 900ms one just because posgres can't handle a different distinct / order by. Doesn't even matter what the outer query order is, it's going to use the index from the inner subquery to find matches first, then re-sort. Happy to do a consulting fee for real solutions to our problems at https://dba.stackexchange.com/questions/260852/fastest-way-to-choose-distinct-rows-and-a-different-order-by-without-using-a-sub – Kevin Parker Mar 23 '20 at 03:38
6

To do a distinct on n columns:

select distinct on (col1, col2) col1, col2, col3, col4 from names
Sunil Kumar
  • 759
  • 7
  • 17
4
SELECT NAME,MAX(ID) as ID,MAX(METADATA) as METADATA 
from SOMETABLE
GROUP BY NAME
David Jashi
  • 4,490
  • 1
  • 21
  • 26
  • 3
    Just a word of caution: that might not return the ID value or the metadata value that belong "together" –  Jun 04 '13 at 09:21
  • @Novum No. It means it cat take a id value from one of the Michael's rows and the metadata from another as it was asked for Michael's maxes. – Clodoaldo Neto Jun 04 '13 at 12:44
  • Well yes, it greatly depends on real data OP uses, which I'm absolutely ignorant of. You may need to use MIN or whatever. Just demonstrated, how you can include fields not on a `GROUP BY` clause. – David Jashi Jun 04 '13 at 12:49
  • 2
    This is not a good solution because different values from different rows will get mixed up. – Elliot Chance Aug 05 '15 at 02:06