CODEWITHSUNDEEP
sqlpostgresql
Homunculus Reticulli
Homunculus Reticulli

Reputation: 68486

Find FIRST lowest value in table AND date on which that lowest value occurs

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

Answers (4)

Gerrat
Gerrat

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

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

Simple and fast solution:

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).

Standard SQL (SQL:2003):

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).

Standard SQL variant (SQL:2008):

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

tkrex
tkrex

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

LSerni
LSerni

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

Related Questions