2

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)

  • First and last entry meaning time or EntryID (or both). – John Powell Dec 21 '14 at 15:13
3

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;
  • Awesome, the first two are easy (And I assume that the sorting happens only after the Where clause have been applied). Can you please explain What a Window function is and how the last two queries work. How do I ensure that the returned record's arrival time is some time on a specified date? – Johan Dec 22 '14 at 5:36
  • P.S. Sorry about my typo, it is AttendanceRecords - that third E is a lower-case E. I do wish postgres wouldn't convert my table and column names to all lower-case. – Johan Dec 22 '14 at 5:38
  • Regarding window functions: there is a tutorial in the manual:: postgresql.org/docs/current/static/tutorial-window.html identifiers are case-insensitive in SQL. So AttendanceREcords is the same name as AttendanceRecords or attendancerecords. The SQL standard requires all (unquoted) identifiers to be stored in UPPERCASE. Postgres chose to store them all in lowercase. – a_horse_with_no_name Dec 22 '14 at 7:16

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.