How to pass record value into a subquery (PostgreSQL)?

Question

I have two files: apt, and appointment_history. The second file has multiple records referencing single records in apt.

How to I get only the last record in appointment history that is referencing the record in apt from within the subquery?

Edit#1: The question is not so much how to group, but rather how to pass an outside value (appointment_recid) into the subquery without grouping the entire appointment_history file on non-used appointment_recid's. So I don't think this is a duplicate question. (Although being a noobie, it might turn out the same).

PostgreSQL 9.3

ERROR: invalid reference to FROM-clause entry for table "apt" SQL state: 42P01 Hint: There is an entry for table "apt", but it cannot be referenced from this part of the query.

select apt.*, h.* 
    from apt 
    join appointment_history h on (h.appointment_recid = apt.appointment_recid)
    join ( 
        select max(tposted) as tposted 
        from appointment_history a 
        where a.appointment_recid = apt.appointment_recid) currenthx 
    on (currenthx.tposted = h.tposted)

TIA

Can you please share some sample data and the result you're trying to get for it? — Mureinik
– Mureinik, Commented Jan 28, 2016 at 15:06
Possible duplicate of Select first row in each GROUP BY group? — pozs
– pozs, Commented Jan 28, 2016 at 15:46

Juan Carlos Oropeza · Accepted Answer · 2016-01-28 20:41:52Z

1

select apt.*, h.* 
    from apt 
    join appointment_history h 
      on (h.appointment_recid = apt.appointment_recid)
    join ( 
           SELECT appointment_recid, max(tposted) as tposted 
           FROM appointment_history a
           GROUP BY  appointment_recid  -- ADD GROUP BY 
         ) currenthx 
      on currenthx.tposted = h.tposted
     and currenthx.appointment_recid = a.appointment_recid   -- JOIN OUTSIDE

For request

select apt.*, h.* 
    from apt 
    join appointment_history h 
      on (h.appointment_recid = apt.appointment_recid)
    join ( 
           SELECT appointment_recid, max(tposted) as tposted 
           FROM appointment_history a
           JOIN apt ap
               on (h.appointment_recid = ap.appointment_recid)               
           GROUP BY  appointment_recid  -- ADD GROUP BY 
         ) currenthx 
      on currenthx.tposted = h.tposted
     and currenthx.appointment_recid = a.appointment_recid   -- JOIN OUTSIDE

edited Jan 28, 2016 at 20:41

answered Jan 28, 2016 at 15:12

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alan Wayne Over a year ago

It seems wasteful to me that the entire appointment history table (500,000 records) needs to be grouped for only about 3 needed records --unless PostgreSQL is somehow optimizing this..??? Thanks.

Juan Carlos Oropeza Over a year ago

You can always create a JOIN inside but using a different alias, so only group the rows produced on the JOIN

Alan Wayne Over a year ago

Sorry...You lost me, how do I do that??

Juan Carlos Oropeza Over a year ago

Check my edit. This wil JOIN both tables and then perform the GROUP BY but you should check performance of both query with EXPLAIN ANALIZE

Patrick · Accepted Answer · 2016-01-28 15:13:02Z

1

In the absence of detailed table information, this probably solves your issue:

SELECT apt.*, h.* 
FROM apt
JOIN (
  SELECT <columns>, max(tposted) AS tposted 
  FROM appointment_history
  GROUP BY <columns>) h USING (appointment_recid);

answered Jan 28, 2016 at 15:13

Patrick

33k7 gold badges73 silver badges102 bronze badges

1 Comment

Alan Wayne Over a year ago

...+1 for demonstrating USING clause to this noobie. Thanks. Grouping on all the columns won't give me the most recent record however when I want only those records for a specific appointment_recid.

Collectives™ on Stack Overflow

How to pass record value into a subquery (PostgreSQL)?

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related