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

Postgresql – get only the record with most recent timestampz

I am frontend engineer building something with supabase, I have written this postgres function

DROP FUNCTION IF EXISTS get_schedule_checklist_data;

CREATE OR REPLACE FUNCTION get_schedule_checklist_data(
    schedule_id UUID,
    blueprint_id_value UUID,
    event_type service_configuration_type
) 
RETURNS TABLE (
    title TEXT,
    schedule_id UUID,
    checklist_id UUID, 
    completed BOOLEAN,
    performed_by TEXT,
    performed_on_date TIMESTAMPTZ,
    parent_id uuid,
    order_number INT8,
    event_type TEXT,
    archived BOOLEAN
)
LANGUAGE sql
AS $function$
SELECT
    bc.title, 
    bcm.schedule_id,  -- This could be null
    bc.id AS checklist_id,
    bcm.completed,
    bcm.performed_by,
    bcm.performed_on_date,
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN
    blueprint_schedule_completion_mapping bcm 
ON
    bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
WHERE
    bc.blueprint_id = blueprint_id_value
    AND bc.event_type = event_type;
$function$;

in this, for bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id condition, I only want the record with most recent performed_on_date in table.

How can I do it?

Tags:

2

Answers


  1. 0

    I think, if do not consider performance, you can get most resent row (rows)

    select *
from(
SELECT
    bc.title, 
    bcm.schedule_id,  -- This could be null
    bc.id AS checklist_id,
    bcm.completed,
    bcm.performed_by,
    bcm.performed_on_date,
    rank()over(partition bcm.schedule_id, bcm.blueprint_checklist_id
                order by bcm.performed_on_date desc) as rank
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN
    blueprint_schedule_completion_mapping bcm 
ON
    bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
WHERE
    bc.blueprint_id = blueprint_id_value
    AND bc.event_type = event_type
)t
where rnk=1;
    Login or Signup to reply.
  2. 0

    Using DISTINCT ON (bc.id, bcm.schedule_id) and ordering by bc.id, bcm.schedule_id, bcm.performed_on_date will return the desired results. The following also addresses ambiguous references in the original code caused by unqualified identifiers:

    CREATE OR REPLACE FUNCTION get_schedule_checklist_data(
    schedule_id UUID,
    blueprint_id_value UUID,
    event_type service_configuration_type
) 
RETURNS TABLE (
    title TEXT,
    schedule_id UUID,
    checklist_id UUID, 
    completed BOOLEAN,
    performed_by TEXT,
    performed_on_date TIMESTAMPTZ,
    parent_id uuid,
    order_number INT8,
    event_type TEXT,
    archived BOOLEAN
)
LANGUAGE sql
AS $function$
SELECT DISTINCT ON (bc.id, bcm.schedule_id)
    bc.title, 
    bcm.schedule_id,  -- This could be null
    bc.id AS checklist_id,
    bcm.completed,
    bcm.performed_by,
    bcm.performed_on_date,
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN
    blueprint_schedule_completion_mapping bcm 
ON
    bcm.schedule_id = get_schedule_checklist_data.schedule_id AND bc.id = bcm.blueprint_checklist_id
WHERE
    bc.blueprint_id = get_schedule_checklist_data.blueprint_id_value
    AND bc.event_type = get_schedule_checklist_data.event_type
ORDER BY bc.id, bcm.schedule_id, bcm.performed_on_date DESC;
$function$;

    If the result of this function is to be joined to other tables, then consider implementing it as a view to take advantage of the query engine’s optimization features. Functions act as optimization fences.

    CREATE OR REPLACE VIEW current_schedule_checklist_data
SELECT DISTINCT ON (bc.id, bcm.schedule_id)
    bc.title, 
    bcm.schedule_id,
    bc.id AS checklist_id,
    bcm.completed,
    bcm.performed_by,
    bcm.performed_on_date,
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN
    blueprint_schedule_completion_mapping bcm 
ON
    bc.id = bcm.blueprint_checklist_id
ORDER BY bc.id, bcm.schedule_id, bcm.performed_on_date DESC;

    (I’ve not yet tested this code since the original post lacked DDL, sample data, and expected output and I haven’t had time to create my own test cases.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search