'Find first record of multiple values in single query

Table

            timestamp             | tracker_id | position 
----------------------------------+------------+----------
 2020-02-01 21:53:45.571429+05:30 |         15 |        1
 2020-02-01 21:53:45.857143+05:30 |         11 |        1
 2020-02-01 21:53:46.428571+05:30 |         15 |        1
 2020-02-01 21:53:46.714286+05:30 |         11 |        2
 2020-02-01 21:53:54.714288+05:30 |         15 |        2
 2020-02-01 21:53:55+05:30        |         12 |        1
 2020-02-01 21:53:55.285714+05:30 |         11 |        1
 2020-02-01 21:53:55.571429+05:30 |         15 |        3
 2020-02-01 21:53:55.857143+05:30 |         13 |        1
 2020-02-01 21:53:56.428571+05:30 |         11 |        1
 2020-02-01 21:53:56.714286+05:30 |         15 |        1
 2020-02-01 21:53:57+05:30        |         13 |        2
 2020-02-01 21:53:58.142857+05:30 |         12 |        2
 2020-02-01 21:53:58.428571+05:30 |         20 |        1

Output

           timestamp             | tracker_id | position 
----------------------------------+------------+----------
2020-02-01 21:53:45.571429+05:30 |         15 |        1
2020-02-01 21:53:45.857143+05:30 |         11 |        1
2020-02-01 21:53:55+05:30        |         12 |        1

How do I find the first record WHERE tracker_id IN ('15', '11', '12') in a single query?

I can find the first record by separately querying for each tracker_id:

SELECT *
FROM my_table
WHERE tracker_id = '15'
ORDER BY timestamp
LIMIT 1;
sqldatabasepostgresqldistinctgreatest-n-per-group


Solution 1:[1]

In Postgres this can be done using the DISTINCT ON () clause:

select distinct on (tracker_id) *
from the_table
where tracker_id in (11,12,15)
order by tracker_id, "timestamp" desc;

Online example

Solution 2:[2]

I have named your timestampl column col1 because I do nto recommend to name your columns with keywords.

select * from mytable m
where m.col1 = (select min(col1)
              from mytable m1
              where m.tracker_id = m1.tracker_id
              group by tracker_id)
and m.tracker_id in (11,15,12);

Here is a small demo

Solution 3:[3]

You can use first_value with the nested select query:

select mt.*
from my_table mt
where mt.timestamp in (
    select first_value(imt.timestamp) over (partition by imt.tracker_id order by imt.timestamp)
    from my_table imt
    where imt.tracker_id in ('11', '12', '15')
)

I'm assuming timestamp is unique, like you said in the comment. You can always replace the joining column with a primary key, like id.

Solution 4:[4]

select distinct on (tracker_id) *
from the_table
where tracker_id in ( select distinct tracker_id from the_table)
order by tracker_id, "timestamp" desc;

Solution 5:[5]

If you want the first row that matches each of your IN values, you can use a window function:

SELECT src.timestamp, src.tracker_id, src.position
FROM (
  SELECT 
    t.timestamp, t.tracker_id, t.position, 
    ROW_NUMBER() OVER(PARTITION BY tracker_id ORDER BY timestamp DESC) myrownum
  FROM mytable t
  WHERE tracker_id IN ('15', '11', '12')
) src
WHERE myrownum = 1 -- Get first row for each "tracker_id" grouping

This will return the first row that matches for each of your IN values, ordering by timestamp.

Solution 6:[6]

Find this Query:

You can uncomment where clause if you want to run query for selected tracker_id

   ;WITH CTE AS
   (
      SELECT ROW_NUMBER() OVER (PARTITION BY tracker_id ORDER BY timestamp) 
      duplicates, * FROM my_table -- WHERE tracker_id IN (15,11,12)
   )
   SELECT timestamp, tracker_id, position FROM CTE WHERE duplicates = 1

Solution 7:[7]

select distinct on (tracker_id) *
from table
where tracker_id in (11,12,15)
order by tracker_id, "timestamp" asc;

i use distinct on when use postgres for this case

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 VBoka
Solution 3
Solution 4 Khalid
Solution 5 ravioli
Solution 6
Solution 7 khoa junior

Related Questions