Reputation: 68486
If I have a table with the following schema:
CREATE TABLE diet_watch (
entry_date date NOT NULL,
weight double precision NOT NULL
);
INSERT INTO diet_watch VALUES ('2001-01-01', 128.2);
INSERT INTO diet_watch VALUES ('2001-01-02', 121.0);
INSERT INTO diet_watch VALUES ('2001-01-03', 122.3);
INSERT INTO diet_watch VALUES ('2001-01-04', 303.7);
INSERT INTO diet_watch VALUES ('2001-01-05', 121.0);
INSERT INTO diet_watch VALUES ('2001-01-06', 128.0);
How would I write a query that would return the FIRST minimum weight between two dates AND the date on which the minimum weight occurs?
Ideally, this will be ANSI-SQL, i.e db agnostic. If I have to be forced to pick an SQL flavor, it will be PostgreSQL, since that is the db I am using.
PSEUDO SQL:
SELECT min(entry_date), min(weight)
FROM diet_watch
where entry date between date1 and date2
group by entry_date;
Upvotes: 1
Views: 2060
Reputation: 29740
SELECT min(entry_date), min(weight) from diet_watch where weight = (
SELECT MIN(weight) as wt from diet_watch where entry_date between date1 and date2
) and entry_date between date1 and date2
[EDIT: Answer tweaked slightly, as it appears that the question was changed from finding the weight at the minimum date to the date at the minimum weight]
Also, the question asks for the "FIRST" minimum weight...which I'll assume means the earliest date with the minimum weight.
Upvotes: 2
Reputation: 659367
SELECT weight, entry_date
FROM diet_watch
WHERE entry_date BETWEEN '2001-01-02' AND '2001-01-06'
ORDER BY 1, 2
LIMIT 1;
Works in PostgreSQL or MySQL and possibly others (not in Oracle or MS SQL Server).
SELECT weight, entry_date
FROM (
SELECT weight, entry_date
,row_number() OVER (ORDER BY weight, entry_date) AS rn
FROM diet_watch
WHERE entry_date between '2001-01-02' and '2001-01-06'
) AS x
WHERE rn = 1;
Not supported by RDBMS which do not implement window functions (like MySQL).
SELECT weight, entry_date
FROM diet_watch
WHERE entry_date BETWEEN '2001-01-02' AND '2001-01-06'
ORDER BY weight, entry_date
FETCH FIRST 1 ROWS ONLY;
Only supported by few RDBMS - find a list on Wikipedia.
The only RDBMS supporting all of these is PostgreSQL (v8.4 or later).
Addressing question in comment, I quote the manual here about ORDER BY:
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.
Emphasis mine. It's just a syntactical shortcut, that is supported by a number of RDBMS. SQL Server is obviously not among them.
Upvotes: 3
Reputation: 59
Working from Gerrat's answer, could just order by entry_date and grab the top row.
SELECT top 1 entry_date, weight
FROM diet_watch
WHERE weight = (SELECT MIN(weight) as wt from diet_watch)
ORDER BY entry_date
EDIT: This is SQL Server code. You could use whatever the equivalent in postgre is, though.
Upvotes: 0
Reputation: 57463
You might request a SELECT * ORDER BY weight ASC LIMIT 1 (most DBs support LIMIT), or not use LIMIT at all and just retrieve the first row from the cursor.
Otherwise, you need a JOIN to get the minimum and then retrieve the date when the minimum did occur; and in case of multiple dates wherein the minimum was attained, you would still require LIMIT and/or a single fetch.
Upvotes: 0