SQL Server 2008 does not have this behaviour.
SELECT id, category_id, name, min(price) as minprice
FROM(
SELECT 1 id ,1 category_id ,'name1' name ,1 price -- Generate some
data.
) AS cat_pictures
GROUP BY category_id;
produced:
Msg 8120, Level 16, State 1, Line 3
Column 'cat_pictures.id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
-----Original Message-----
From: Keith Medcalf <[email protected]>
To: General Discussion of SQLite Database <[email protected]>
Sent: Wed, Aug 27, 2014 5:25 pm
Subject: Re: [sqlite] Window functions?
On Wednesday, 27 August, 2014 13:17, Petite Abeille said:
>On Aug 26, 2014, at 2:09 AM, Keith Medcalf <[email protected]> wrote:
>> select id, category_id, name, min(price) as minprice
>> from cat_pictures
>> group by category_id;
>>
>> Done. And no need for any windowing functions ...
>This peculiar behavior is very unique to SQLite.
Not really. Sybase, SQL Server and DB2 do (or did do) the same thing.
>Most reasonable SQL engines will throw an exception when confronted
>with the above. SQLite calls it a feature. I personally see it as a
>misfeature. ( Ditto with tagging an implicit limit 1 to scalar
>queries. Anyway. )
Well, I kind of like the former (group by) behaviour. Tacking of an automatic
"limit 1" on a scalar subquery may lead one to make bad assumptions about the
shape of one's data, however, if one actually knows what one is doing, I don't
think this is a problem either.
>On the other hand, one could look at the current 'group by' behavior as
>exhibited by SQLite as a precursor to a proper, more formalize, handling
>of analytic functions.... :)
Perhaps. On the other hand, I really do not understand why people want
"analytic functions" -- we did perfectly well analyzing data long before they
were invented. But then again I cannot understand why people think that
Relational Databases using SQL are "better" for everything than good
old-fashioned Network-Extended Navigational Databases. But then again, maybe
I'm just an old fart ...
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
