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,
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
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
The main difference between HAVING and WHERE ist that WHERE operates on the
input set and HAVING operates on the output set.
If your condition requires computing an aggregate, then HAVING is a viable
method of avoiding a subquery that needs to repeatedly scan the input table:
SELECT
7 matches
Mail list logo
