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, Jan 28 '16 at 15:06
Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — pozs, Jan 28 '16 at 15:46

Juan Carlos Oropeza · Accepted Answer · 2016-01-28T20:41:52.833

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 '16 at 20:41

answered Jan 28 '16 at 15:12

Juan Carlos Oropeza

47,252
12
78
118

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. – Alan Wayne Jan 28 '16 at 19:18
You can always create a `JOIN` inside but using a different alias, so only group the rows produced on the `JOIN` – Juan Carlos Oropeza Jan 28 '16 at 19:44
Sorry...You lost me, how do I do that?? – Alan Wayne Jan 28 '16 at 19:55
1

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` – Juan Carlos Oropeza Jan 28 '16 at 20:42

score 1 · Answer 2 · answered Jan 28 '16 at 15:13

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 '16 at 15:13

Patrick

29,357
6
62
90

...+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. – Alan Wayne Jan 28 '16 at 19:53

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

2 Answers2