4 ms·
To have the first element, you have to describe a relation of order. It's not ordered by default.
by helge9210 2y ago
To have the first element, you have to describe a relation of order. It's not ordered by default.
- cyberax 2y agoActually, you don't have to. Any first element would do, even if it's a random one. It's perfectly possible to do `SELECT * FROM blah LIMIT 1`, after all.
- _dain_ 2y agoYou can use ANY_VALUE to get an arbitrary value from a GROUP BY. There's also FIRST_VALUE for a window function (though that requires you specify an order).
- cyberax 2y agoThis is a non-standard extension, and it also doesn't allow the selection of the _first_ element in a group. Even if the group is actually ordered.
- calmbonsai 2y agoTrue, but even going all the way back to E.F. Codd, there's "cheating" involved with all sorts of implicit sorting. Even now, the default behavior of SQLite is still "undefined" if a Select column doesn't appear in Group By. Well, to be precise it's a single "arbitrary chosen row" used to sort the results. https://www.sqlite.org/lang_select.html#resultset https://www.sqlite.org/lang_select.html#resultset I'm not bagging on SQLite here (I love it!), but I am bagging on SQL 'cuz EVERY implementation must make far too many of these sorts of Faustian bargains.
- disgruntledphd2 2y agoThat sqlite one is really annoying, many other dialects give you an error.
- cyberax 2y agoBut I can order it: https://news.ycombinator.com/item?id=42911873 https://news.ycombinator.com/item?id=42911873