Question 89767 in PostgreSQL Question posted in
The official documentation can be found here.

How to get a single record in a one-to-many relationship – Postgresql

In a one-to-many relationship, users have many contracts. However, I only want the most recent contract for a specific set of users.

My query to get all contracts for users look like this:

SELECT userid FROM contract 
WHERE userid IN (123, 143, 153, 163);

I naively thought the following query could return the most recent contract for the 4 users in the WHERE clause. However, it only limits to 1 record for the entire result set.

SELECT userid FROM contract 
WHERE userid IN (123, 143, 153, 163)
ORDER BY signingdate DESC LIMIT 1 OFFSET 0

How can I fix my query to get the latest records that have a one-to-many relationship?

Tags:

3

Answers


  1. 0

    If you know how to make a subselect or with common table expression query (CTE), then the following will work for you. This numbers the results within each user ID, ordering by the signing date.

     select userId from (
       select userId, 
              row_number() over (partition by userId order by signingdate desc) as rnDmy
       from contract
       where userId in (123, 143, 153, 163)
      ) where rnDmy = 1
    Login or Signup to reply.
  2. 0

    This should Work

    SELECT DISTINCT userid, 
MAX(signingdate) OVER (PARTITION BY userid) 
FROM contract 
where userid in (123, 143, 153, 163)

    UPDATE: added DISTINCT to eliminate possibility of duplicates

    Login or Signup to reply.
  3. 0

    Rather than limit and offset use select distinct on:

    select distinct on(userid) 
       userid
     , signingdate
  from contract 
 where userid in (123, 143, 153, 163)
order by userid
       , signingdate desc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search