All of the responses I've seen to this question so far seem logically wrong, or
at the very least are different than I would do it, and my proposal is one that
should work reliably on any DBMS. You use a subquery in the FROM clause.
select currency, price
from (
select currency, day, max(tim
On Tue, 29 Sep 2015 15:05:42 +0800
Rowan Worth wrote:
> Imagine a DB storing a history of currency exchange rate info.
>
> CREATE TABLE Prices (
> id INTEGER PRIMARY KEY,
> day INTEGER,
> currency TEXT,
> price FLOAT);
Better for your purpose would be:
CREATE TABLE Prices (
Hi guys,
Imagine a DB storing a history of currency exchange rate info.
CREATE TABLE Prices (
id INTEGER PRIMARY KEY,
day INTEGER,
currency TEXT,
price FLOAT);
Assume 'day' increases monotonically and there is at most one price
recorded per currency per day - if you want to know
On Tue, Sep 29, 2015 at 6:16 AM, Simon Slavin wrote:
> I don't like using sub-SELECT and I would love to see another reader
> rephrase this using 'WITH' or a VIEW.
>
with sub as (select currency, price, max(day) from prices group by currency)
select currency, price from sub;
On 29 Sep 2015, at 8:05am, Rowan Worth wrote:
> CREATE TABLE Prices (
>id INTEGER PRIMARY KEY,
>day INTEGER,
>currency TEXT,
>price FLOAT);
>
> Assume 'day' increases monotonically and there is at most one price
> recorded per currency per day - if you want to know the latest pr
Rowan Worth wrote:
> if you want to know the latest prices, is this legal?
>
> SELECT currency, price FROM Prices GROUP BY currency HAVING time = MAX(time);
It is not legal ANSI SQL, and most other databases will complain.
While the WHERE clause allows to filter out rows from the table, the
HAVIN
gliche Nachricht-
Von: Rowan Worth [mailto:rowanw at dugeo.com]
Gesendet: Dienstag, 29. September 2015 09:06
An: General Discussion of SQLite Database
Betreff: [sqlite] Is it legal SQL to select a specific row of a group via a
HAVING clause?
Hi guys,
Imagine a DB storing a history of cu
7 matches
Mail list logo
