I have a table us_customers that looks like this (with hundreds of thousands of rows):

|    id    | us_state |
| 12345678 | MA       |
| 23456781 | AL       |
| 34567812 | GA       |
| 45678123 | FL       |
| 56781234 | AZ       |
| 67812345 | MA       |
| 78123456 | CO       |
| 81234567 | FL       |

... and I want to SELECT a sample of n customers from each us_state.

Is there a way to do that cleanly in PostgreSQL 9.3?

I can get one customer from each us_state easily with:

SELECT DISTINCT ON (us_state) id
FROM us_customers
ORDER BY us_state;

But if I want, say, three customers from each state, is there a way I can do this without running the same query multiple times?

  • Remember to always declare your version of Postgres. Assuming current Postgres 9.5 ... – Erwin Brandstetter Mar 26 '16 at 3:45
  • Good call. I'm on 9.3 actually. – Shaun Scovil Mar 26 '16 at 11:42

You can number and order id by us_state using the ROW_NUMBER() Window Function and only keep the n first values:

    , ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n
  FROM data
) as ord
WHERE n <= 2
ORDER BY us_state

Or you can CROSS JOIN with a subquery:

  SELECT DISTINCT us_state FROM data
) as s
  FROM data d
  WHERE d.us_state = s.us_state
) as l
ORDER BY l.us_state
  • Sample SQL Fiddle here
  • I used a small sample of 1 to 3 rows per state. Therefore I limit only to 2 values
  • I ordered it by ids but you can change that and order by whatever works best for you

Output with my small sample:

       id | us_state | n
      123 |       AL | 1 
      456 |       AL | 2 
 56781234 |       AZ | 1 
 78123456 |       CO | 1 
 45678123 |       FL | 1 
 81234567 |       FL | 2 
 34567812 |       GA | 1 
      123 |       MA | 1 
      456 |       MA | 2 

Note that n is the result of ROW_NUMBER and doesn't exist in the second query. On a big table, an index on the partition (us-state) and order (id here) columns will help.

Sample used:

    ("id" int, "us_state" varchar(2))

    ("id", "us_state")
    (12345678, 'MA'),
    (123, 'MA'),
    (456, 'MA'),
    (23456781, 'AL'),
    (123, 'AL'),
    (456, 'AL'),
    (34567812, 'GA'),
    (45678123, 'FL'),
    (56781234, 'AZ'),
    (67812345, 'MA'),
    (78123456, 'CO'),
    (81234567, 'FL')
| improve this answer | |
  • Both queries have to read the whole table (repeatedly) in Postgres and are much slower than necessary since Postgres does not have loose index scans (yet). There are ways around it. – Erwin Brandstetter Mar 26 '16 at 3:43
  • @ErwinBrandstetter if there is a us_states table and we replace it for s in the 2nd query, it will not read the whole table, corerct? (I mean there is not much difference between Julien's CROSS JOIN LATERAL and yours LEFT JOIN LATERAL (always assuimng there is a states tables we can use instead of the select distinct subquery). – ypercubeᵀᴹ Mar 26 '16 at 8:49
  • @ypercubeᵀᴹ:That's right. The expensive part in the 2nd query is the SELECT DISTINCT us_state FROM data because there are few distinct values with many rows, as explained here. The rest is cheap either way assuming we have the mentioned index(es), best a multicolumn index on (us_state, id). Else the (uncalled) ORDER BY in the LATERAL subquery gets expensive, too. I only chose LEFT JOIN to include all states in the result either way, which probably makes sense once we have a us_states table. – Erwin Brandstetter Mar 26 '16 at 14:04

With us_states table

If you don't happen to have another table with a complete set of possible values for us_state then I would urgently suggest to create it:

CREATE TABLE us_states (
  us_state varchar(2) PRIMARY KEY
  -- ... more columns?

For one, you may want to add a FK constraint on us_customers.us_state to enforce legal values. But more to the point, it allows a substantially faster query:

SELECT u.us_state, d.id  -- or more columns?
FROM   us_states u
   SELECT id             -- or more columns?
   FROM   us_customers
   WHERE  us_state = u.us_state
   LIMIT  3              -- three customers from each state
   ) d ON true
ORDER  BY u.us_state;

Extracting 50 distinct values from "hundreds of thousands of rows" with DISTINCT every time can be more expensive than the rest of the query and would be a big waste of time.

Do not add ORDER BY to the subquery, that's cheaper. Since you did not define which rows to pick, an arbitrary selection is good enough.

Make that a LEFT JOIN to include every state at least once, even if no matching rows in us_customers are found.

Without us_states table

If you don't have a us_states table, there are still substantially faster ways than with the standard techniques suggested by @Julien. Emulate a loose index scan with a CTE. (You need the index outlined below.):

  (SELECT us_state FROM data ORDER BY us_state LIMIT 1)  -- parentheses required

   SELECT (SELECT us_state FROM data WHERE us_state > u.us_state
           ORDER BY us_state LIMIT 1)                    -- correlated subquery
   FROM   us_states u
   WHERE  u.us_state IS NOT NULL
SELECT us_state FROM us_states
WHERE  us_state IS NOT NULL;

This query can be a drop-in replacement for the missing us_states table, or it can be used to create the missing table.

  (SELECT us_state FROM us_customers ORDER BY us_state LIMIT 1)

   SELECT (SELECT us_state FROM us_customers WHERE us_state > c.us_state
           ORDER BY us_state LIMIT 1)
   FROM   us_states c
   WHERE  c.us_state IS NOT NULL
SELECT u.us_state, d.id          -- or more columns?
FROM   us_states u
   SELECT id                     -- or more columns?
   FROM   us_customers
   WHERE  us_state = u.us_state  -- eliminates NULL value from CTE
   LIMIT  3                      -- three customers from each state
   ) d
ORDER  BY u.us_state;

Using CROSS JOIN this time since the CTE only found existing values anyway and we don't need to add WHERE us_state IS NOT NULL this way.

Or, if you can guarantee there are at least 3 rows per us_state, this would be very fast:

  (SELECT us_state, id FROM us_customers3 ORDER BY us_state LIMIT 3)

   SELECT u.us_state, id
   FROM  (SELECT us_state FROM cte LIMIT 1) c
   ,      LATERAL (
      SELECT us_state, id
      FROM   us_customers3
      WHERE  us_state > c.us_state
      ORDER  BY us_state
      LIMIT  3
      ) u
ORDER BY us_state;

The last query fails if any state has less than three rows.


Either way, create a multicolumn index like this!

CREATE INDEX data_covering_idx ON data (us_state, id);

SQL Fiddle.

Related, with detailed explanation:

| improve this answer | |
  • Very cool, thanks for the detailed explanation! I'll try this on Monday and see how it goes. – Shaun Scovil Mar 26 '16 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.