Fetching FIRST lowest value in table and the date on which it occurs, for every distinct user

Question

I am trying to find the first lowest value in a table, and the date on which it occurs, for every DISTINCT user in a table.

This is the table schema and some sample data:

CREATE TABLE diet_watch (
  entry_date date NOT NULL,
  user_id    int default 1,
  weight     double precision NOT NULL
);

INSERT INTO diet_watch VALUES ('2001-01-01', 1, 128.2);
INSERT INTO diet_watch VALUES ('2001-01-02', 1, 121.0);
INSERT INTO diet_watch VALUES ('2001-01-03', 1, 122.3);
INSERT INTO diet_watch VALUES ('2001-01-04', 1, 303.7);
INSERT INTO diet_watch VALUES ('2001-01-05', 1, 121.0);
INSERT INTO diet_watch VALUES ('2001-01-01', 2, 121.0);
INSERT INTO diet_watch VALUES ('2001-01-06', 2, 128.0);

The SQL I came up with is here in this snippet

I have since been informed that it is incorrect, perhaps someone can explain what the problem with my SQL is ?

Note: I would prefer ANSI SQL if possible, but I am using PostgreSQL, so if I have to use a specific flavour of SQL, it has to work on PG.

Answer 1

Note: Not sure if Window functions are ANSI SQL

WINDOW Functions are a part of the SQL:2003 specification: http://en.wikipedia.org/wiki/Window_function_%28SQL%29#Window_function (Thx @a_horse_with_no_name)

Try this:

http://sqlfiddle.com/#!1/7aa4e/22

SELECT *
  FROM 
    (
     SELECT a.*, 
            ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY weight) AS Position
       FROM diet_watch a

    ) a
    WHERE a.Position = 1 

Answer 2

First, your query is needlessly complicated. You can just put the group by in the subquery and eliminate the outer query.

The windows function, mentioned by @Chandu, is a very good solution. It is ANSI SQL and postgres supports it. However, not all databases do. An alternative is:

select dw.*
from diet_watch dw join
     (select user_id, min(entry_date) as mindate
      from diet_watch dw
      group by user_id
     ) dwmin
     on dw.user_id = dwmin.user_id and dw.entry_date = dwmin.mindate

The reason your original query doesn't work is because the minimum entry_date may not have the minimum weight. Your query retrieves the minimum of each field independently. This version finds the minimum date, then joins that back to the original table to get the weight (and other information) on that day.