Geek asks and answers

Getting FIRST the smallest value in the table and the date of its occurrence for each individual user

I am trying to find the first minimum value in the table and the date on which it occurs for each DISTINCT user in the table.

This is a table diagram 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

Since then I have been told that this is wrong, maybe someone can explain what the problem is with my SQL?

Note. I would prefer ANSI SQL if possible, but I use PostgreSQL, so if I need to use a special SQL flavor, it should work on PG.

+5
source share
3 answers

Note. Not sure if window functions are ANSI SQL

WINDOW SQL: 2003: http://en.wikipedia.org/wiki/Window_function_%28SQL%29#Window_function ( @a_horse_with_no_name)

:

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
+6

-, . .

windows, @Chandu, . ANSI SQL, postgres . . :

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

, , , entry_date . . , , ( ) .

+1

, , , . , 1 121 , ? , SQL.

SELECT min(dw.entry_date), dw.user_id, dw.weight FROM diet_watch dw,
    (SELECT min(weight) AS "weight", user_id FROM diet_watch GROUP BY user_id) mins
WHERE dw.user_id = mins.user_id AND dw.weight = mins.weight
GROUP BY dw.user_id, dw.weight

An internal selection finds each user minimum weight. Another minute is needed per day, because otherwise you will not specifically choose the first time that the minimum weight has been reached for this user.

http://sqlfiddle.com/#!1/7aa4e/51/0

+1
source

More articles:


All Articles