Subquery with two columns

Question

I have two tables, like so:

a.id, a.created, a.b
b.id, b.name, b.created

I want all the records from the table b to be sorted by the a.created timestamp.

I've been trying something of the following:

SELECT b.id, b.name FROM b JOIN a ON a.b = b.id ORDER BY b.created DESC

But it always returns multiple records from b when really all I want is one match of a for every row of b.

I tried to use a SELECT subquery:

SELECT b.id, b.name, (SELECT a.created AS a_c FROM a WHERE
a.b = b.id) AS a_c FROM b ORDER BY a_c DESC

But I also want to be able to pull a.id as well.

Is there a JOIN statement I could use or should I use two subqueries? This is going to be a pretty important function in my application so I want the performance to be above par!

Did you try ? SELECT DISTINCT b.id, b.name FROM b JOIN a ON a.b = b.id ORDER BY b.created DESC — Kiran Hegde, Jul 13 '14 at 13:15
If you want to pull `a.id` as well, and there are multiple matching rows of `a`, which one do you want to pull? — Barmar, Jul 13 '14 at 13:22
@Barmar I want to match the newest record of `a`. e.g. `ORDER BY a.created DESC LIMIT 1` — sheppardzw, Jul 13 '14 at 13:23

score 1 · Answer 1 · answered Jul 13 '14 at 13:31

SELECT b.id AS bid, b.name, a.id AS aid, a.created
FROM b
JOIN (SELECT a1.id, a1.b, a1.created
      FROM a AS a1
      JOIN (SELECT id, MAX(created) AS maxcreate
            FROM a
            GROUP BY id) AS a2
      ON a1.id = a2.id AND a1.created = a2.maxcreate) AS a
ON b.id = a.b
ORDER BY a.created DESC

The subquery gets the row in a with the newest create date for each id. Then you join this with b.

score 1 · Answer 2 · answered Jul 13 '14 at 13:39

1

select b.id, b.name, a.id a_id, a.created
from b join (
    select b, max(a.created) max_created
    from a
    group by b
) t1 on t1.b = b.id
join a on a.b = b.id and a.created = t1.max_created
order by a.created desc

answered Jul 13 '14 at 13:39

FuzzyTree

32,014
3
54
85

score 1 · Answer 3 · answered Jul 13 '14 at 13:43

select
   b.id
  ,b.name
  ,b.created as b_created
  ,a.created as a_created 
from b
left join a
join (
   select b, max(created) as max_creatd
   from a
   group by b
)a_min
   on a_min.b = a.b
  and a_min.max_creatd = a.created
   on a.b   = b.id

which for these datasets (SQL Server syntax to avoid writing a ton of DDL):

a as (select * from (values
     ('A1','20140701 23:11:59', 'B1')
    ,('A2','20140701 22:11:59', 'B1')
  )a(id,created,b)

and

b as ( select * from (values
    ('B1', 'Bob', '20140701 11:59:59')
   ,('B2', 'Bub', '20140701 10:59:59')
  )b(id,name,created)

returns the following:

id   name b_created         a_created
---- ---- ----------------- -----------------
B1   Bob  20140701 11:59:59 20140701 23:11:59
B2   Bub  20140701 10:59:59 NULL

(2 row(s) affected)

score 0 · Answer 4 · edited May 23 '17 at 11:50

0

I ended up just joining the tables as suggested in this answer.

SELECT b.id, b.name, a.id AS a_id FROM b JOIN a ON
a.id = (SELECT a.id FROM a WHERE a.b=b.id ORDER BY 
a.created DESC LIMIT 1)

edited May 23 '17 at 11:50

Community

1
1

answered Jul 13 '14 at 13:57

sheppardzw

924
1
7
15

Subquery with two columns

4 Answers4