sql order of nesting

Question

This is a clarification on this post:

SQL select only rows with max value on a column

In the accepted answer, the nested query is the one used for the max computation and the outer query joins to that. I tried to reverse the order but ran into a syntax error.

Query:

(SELECT id, MAX(rev) mrev
FROM docs 
GROUP BY id) b
join (select id, rev, content from docs) d
on b.id = d.id and d.rev = b.rev

There error I run into is this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b join (select id, rev, content from docs) d on b.id = d.id and d.rev = b.rev' at line 3

Does the order matter here?

Here is the link: http://sqlfiddle.com/#!9/a6c585/64570

How could I comment on your query if I hadn’t looked and tried to execute it? — Joakim Danielson, Jul 28 '18 at 15:02

LukStorms · Accepted Answer · 2018-07-28T15:58:29.730

1

You can write that query like this.

SELECT d.*
FROM
(
  SELECT id, MAX(rev) AS maxrev
  FROM docs 
  GROUP BY id
) b
JOIN docs AS d
ON (b.id = d.id AND d.rev = b.maxrev)

Notice how it selects from a sub-query for the max rev. While the sub-query is simply joined to the table.

Another way to write it :

select d.*
from docs d
join (
  select id, max(rev) maxrev
  from docs
  group by id
) b
on b.id = d.id and b.maxrev = d.rev

Or if you dare to use an EXISTS :

SELECT *
FROM docs AS d
WHERE EXISTS (
  SELECT 1
  FROM docs AS b
  WHERE b.id = d.id 
  GROUP BY b.id
  HAVING d.rev = MAX(b.rev)
);

edited Jul 28 '18 at 15:58

answered Jul 28 '18 at 14:59

LukStorms

28,916
5
31
45

Thanks LukStorms, I know that. But I am trying to reverse the order. Do you know why my attempt does not work? – ganesh reddy Jul 28 '18 at 15:02
So you want the MIN instead of the MAX? – LukStorms Jul 28 '18 at 15:03
Check my sqlfiddle link and please explain why that query does not work. – ganesh reddy Jul 28 '18 at 15:04
It has 2 errors. 1) you start with a sub-query. Instead of selecting from one. 2) the ON clause doesn't know a b.rev – LukStorms Jul 28 '18 at 15:08
I see that, so is it always true that subqeries need to be following onto a general select, in other words you cannot begin a query with a nested subquery? Can you please elaborate more on the second error? – ganesh reddy Jul 28 '18 at 15:14
If you watch what's in sub-query b of your SQL. It only outputs the fields id and mrev. So only b.id and b.mrev are known to the outer query. – LukStorms Jul 28 '18 at 15:23

sql order of nesting

1 Answers1