On Wed, 29 Oct 2014 20:38:07 +0200
Baruch Burstein <bmburst...@gmail.com> wrote:
> If I have a table, "t", with 2 columns, "a" and "b". Assuming that
> "a" is a unique number, will the following query always return the
> whole row (that is, with the correct "b" column) where "a" is the
> highest number below 50?
>
> SELECT max(a), b FROM t WHERE a<50;
Standard syntax would be
select * from t where a = (select max(a) from t where a < 50);
If several rows meet the criteria (have the same value of "a"), you'll
get all of them. If that's not what you want, you need more criteria,
e.g.,
select a, min(b) as "first b" from t
where a = (select max(a) from t where a < 50)
group by a;
Interestingly, your query should be expressible as an existence
test,
select * from t as T where exists (
select 1 from t
where a < 50
having max(a) = T.a
);
but in SQLite that produces a syntax error,
Error: near line 15: a GROUP BY clause is required before HAVING
I suggest that's an unnecessary requirement. Lack of a GROUP BY --
combined with a lack of column names in the SELECT clause -- is a truth
test: it either produces a row or not. In fact, the very strange
select min(b) from t having max(a) = 50;
is permissible afaik by the standard. (Just an illustration, not your
query.)
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
