Returning The First X Records in a Postgresql Query with a Unique Field

Returning the first X records in a postgresql query with a unique field

In a single SQL query, it can be achieved easily with SELECT DISTINCT ON... which is a PostgreSQL-specific feature.

See http://www.postgresql.org/docs/current/static/sql-select.html

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

With your example:

 SELECT DISTINCT ON (other_id) * 
 FROM instances
 WHERE user_id = 3 
 ORDER BY other_id LIMIT 10

How to select only the first rows for each unique value of a column?

A very simple answer if you say you don't care which address is used.

SELECT
    CName, MIN(AddressLine)
FROM
    MyTable
GROUP BY
    CName

If you want the first according to, say, an "inserted" column then it's a different query

SELECT
    M.CName, M.AddressLine,
FROM
    (
    SELECT
        CName, MIN(Inserted) AS First
    FROM
        MyTable
    GROUP BY
        CName
    ) foo
    JOIN
    MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

Return X elements before and after sorted by a property that is not unique SQL

You can use window functions:

select t.*
from (select t.*,
             count(*) filter (where a = 'bd') over (order by b, a rows between x preceding and x following) as cnt
      from t
     ) t
where cnt > 0;

Here is a db<>fiddle.

Finding x rows with distinct value in col1 but same value in col2

WITH distinctdevs AS (SELECT DISTINCT dev FROM int_state) 
SELECT distinctdevs.dev, (SELECT int FROM int_state ints WHERE NOT EXISTS 
 (SELECT dev FROM distinctdevs WHERE NOT EXISTS (
   SELECT * FROM int_state WHERE 
     int_state.int = ints.int AND int_state.dev = distinctdevs.dev
   )
  ) LIMIT 1) FROM distinctdevs;

You might actually get better performance if you don't reuse the CTE on line 3, as that might trick the optimizer into thinking this is a correlated subquery rather than a constant. The core trick here is the doubly nested WHERE NOT EXISTS, converting "an int that appears for every dev" to the logically equivalent "an int for which there is no dev for which it does not appear."

SQL to return distinct records with priority on first found item

Use a CASE statement in the ORDER BY clause :

SELECT * FROM t;
┌────┬─────┬────┐
│ id │ tag │ pk │
├────┼─────┼────┤
│  1 │ 111 │  1 │
│  2 │ 111 │  2 │
│  2 │ 112 │  3 │
│  3 │ 111 │  4 │
│  4 │ 333 │  5 │
│  4 │ 334 │  6 │
│  4 │ 111 │  7 │
│  5 │ 335 │  8 │
└────┴─────┴────┘
(8 rows)

---

SELECT DISTINCT ON (id) *
FROM t 
ORDER BY id, (CASE tag WHEN 111 THEN 0 ELSE 1 END);
┌────┬─────┬────┐
│ id │ tag │ pk │
├────┼─────┼────┤
│  1 │ 111 │  1 │
│  2 │ 111 │  2 │
│  3 │ 111 │  4 │
│  4 │ 111 │  7 │
│  5 │ 335 │  8 │
└────┴─────┴────┘
(5 rows)

How to get First and Last record from a sql query?

[Caveat: Might not be the most efficient way to do it]:

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1)

UNION ALL

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC    
LIMIT 1)

SQL return a Distinct column and the first date of the distinct column

Are the records sorted?

select url, min(created_at)
from databaseTable
where blabala
group by url

Select first row in each GROUP BY group?

On databases that support CTE and windowing functions:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

---

Related Topics