On Sun, May 8, 2016 at 7:45 PM, dandl <david at andl.org> wrote:
> Just to add to the below:
>
> S# | SNAME | STATUS | CITY
> -------------------------------
> S1 | Smith | 20 | London
> S2 | Jones | 10 | Paris
> S3 | Blake | 30 | Paris
> S4 | Clark | 20 | London
> S5 | Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> ------
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
My guess based on the available data is that, since you don't specify which
"S#" you want associated with each city, it is picking the max of each
(coincidentally). If you want the minimum S# value, this seems to work:
select distinct city from s group by city order by min("S#") asc;
I'm not sure if that *should* work per "standard" SQL, but it does with
SQLite. I'd have expected something like this to be necessary:
select city, min("S#") as x from s group by city order by x asc;
And if you only want the city:
select city from (select city, min("S#") as x from s group by city order by
x asc);
But I'm not a SQL master.
Distinct used with group by seems redundant, but again, I might just not
understand how they are useful together.
--
Scott Robison
