PostgreSQL – How to Select the First or Last Entry for a Specific Day

greatest-n-per-grouppostgresql-9.3

I have a table "AttendanceRecords" with entries like this

EntryID UUID PRIMARY KEY,
StafID UUID REFERENCES staffmembers(ID) NOT NULL,
ArrivalTime TIMESTAMP NOT NULL,
DepartureTime TIMESTAMP

For one function I want to find the first and last entry for a specified staff member on a specified date. To Clarify, First would be as per

SELECT MIN(ArrivalTime) 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx';

But I'm looking for how to compose a query that will do

SELECT EntryID 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx' 
AND ArrivalTime = MIN(ArrivalTime);

On a related report I would like to find all the entries where the arrival-times are on a specified date, eg sorted by StaffID

What would the queries look like for these (Using Postgres 9.3)

Best Answer

You can't use an aggregate function in a condition, you need to use a sub-select

SELECT EntryID 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx' 
AND ArrivalTime = (select min(ArrivalTime) 
                   from AttendanceREcords
                   where StaffID = 'xxxxx');

But this can be done more efficiently by using a LIMIT clause:

SELECT EntryID 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx' 
ORDER BY arrivalTime 
LIMIT 1

There is a difference between the two statements: if more than one row has the same minimum arrival time, the first one will return all of them, the second one only one row.

Another alternative that is usually more efficient that a sub-select is using a window function:

SELECT EntryID
FROM (
  SELECT EntryID, 
         dense_rank() over (order by arrivalTime) as rnk
  FROM AttendanceREcords 
  WHERE StaffID = 'xxxxx' 
) t
where rnk = 1;

By changing the order by arrivalTime you can select the first or the last.

If you want the first and last in a single query you can do something like this:

SELECT EntryID,
       arrivalTime
FROM (
  SELECT EntryID, 
         min(arrivalTime) as min_time,
         min(arrivalTime) as max_time,
  FROM AttendanceREcords 
  WHERE StaffID = 'xxxxx' 
) t
where arrival_time = min_time
   or arrival_time = max_time;

Related Solutions

Oracle – How to Select the First Row of Each Group

This solution also uses keep, but val and kind can also be simply calculated for each group without a subquery:

select min(id) keep(dense_rank first order by kind) id
     , val
     , min(kind) kind
  from mytable
 group by val;

ID |  VAL | KIND
-: | ---: | ---:
 3 |    3 |    4
 2 | 1337 |    1

dbfiddle here

KEEP…FIRST and KEEP…LAST are an Oracle-specific feature of aggregates — you can read about then here in the Oracle docs, or on ORACLE_BASE:

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

First, to answer the questions implied in the comments, that the assignment of row numbers with the ROW_NUMBER() aggregate seems inefficient because we already have the COUNT(violation) numbers:

This is needed because the COUNT numbers can be different for each partition (class). Since we want the 2 top numbers (for each class), we can't find a useful condition for that. With the row numbers, we can use the WHERE v < 3 which gives us the top 2.

In version 9.3, the LATERAL joins were added in Postgres, which are similar to the CROSS and OUTER APPLY of SQL-Server. With this new kind of join, you can write a query that uses the COUNT numbers and a TOP 2 for each partition. Whether it is more or less efficient, you can test:

WITH classes AS
  ( SELECT DISTINCT class
    FROM people
  ) 
                             -- if you have a "classes" table, skip the above lines
SELECT 
    v.id, v.name, c.class,
    v.violations
FROM
    classes AS c,
  LATERAL 
    ( SELECT p.id, p.name,
             COUNT(d.violation) AS violations
      FROM people AS p
        INNER JOIN discipline AS d
          ON d.people_id = p.id
      WHERE p.class = c.class 
      GROUP BY p.id, p.name
      ORDER BY violations DESC
      LIMIT 2
  ) AS v
ORDER BY
    c.class, v.violations DESC ;

Tested at SQL-Fiddle

Best Answer

Related Solutions

Oracle – How to Select the First Row of Each Group

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

Related Question