Get the max elements in a join?
-
I have two tables 1. A table
USERwith aUSER_ID, NAME, ...2. A tableSTATUSwith aSTATUS_ID, PEOPLE_FK(foreign key to USER_ID),LABEL, DATE_START...A USER can only have one active status. I use the DATE_START field to keep the history of all status of the user. I never delete a status. If I create a join between USER and STATUS I can easily get all my users with all they status. BUT I would like to create a query to retrieve all my users with they last status only. I'm still learning SQL. What should I use? Is a select in a select recommended? I would like to avoid the solution of with subquesries. EDIT -- I searched for a duplicate of this question without success. If this question has been already answered just show me the link. thank you. -
I have two tables 1. A table
USERwith aUSER_ID, NAME, ...2. A tableSTATUSwith aSTATUS_ID, PEOPLE_FK(foreign key to USER_ID),LABEL, DATE_START...A USER can only have one active status. I use the DATE_START field to keep the history of all status of the user. I never delete a status. If I create a join between USER and STATUS I can easily get all my users with all they status. BUT I would like to create a query to retrieve all my users with they last status only. I'm still learning SQL. What should I use? Is a select in a select recommended? I would like to avoid the solution of with subquesries. EDIT -- I searched for a duplicate of this question without success. If this question has been already answered just show me the link. thank you.The probably best solution is to add an ACTIVE column to your STATUS table and make an unique index from ACTIVE and USER_ID. So you never ever will be able to set two rows to active state - or you will try and got an SQL error... It will also make your query simple as you always ask for WHERE ACTIVE = 1...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
I have two tables 1. A table
USERwith aUSER_ID, NAME, ...2. A tableSTATUSwith aSTATUS_ID, PEOPLE_FK(foreign key to USER_ID),LABEL, DATE_START...A USER can only have one active status. I use the DATE_START field to keep the history of all status of the user. I never delete a status. If I create a join between USER and STATUS I can easily get all my users with all they status. BUT I would like to create a query to retrieve all my users with they last status only. I'm still learning SQL. What should I use? Is a select in a select recommended? I would like to avoid the solution of with subquesries. EDIT -- I searched for a duplicate of this question without success. If this question has been already answered just show me the link. thank you.CREATE TABLE statuser
([userid] int)
;INSERT INTO statuser
([userid])
VALUES
(1),
(2)
;CREATE TABLE stat
([userid] int, [status] int, [startdate] date)
;INSERT INTO stat
([userid], [status], [startdate])
VALUES
(1, 1, '2012-05-30'),
(1, 2, '2013-06-23')
;
with maxstat as (
select s.userid,max(startdate) startdate
from statuser su
left outer join stat s
on su.userid = s.userid
group by s.userid
)
select s.userid,s.status,s.startdate
from maxstat m
join stat s
on m.userid = s.userid
and m.startdate = s.startdateOh, also make a habit of having the same name of the fields in all tables, so if it's called
USER_IDin one table don't call itPEOPLE_FKin another. It's much easier to debug that way.Wrong is evil and must be defeated. - Jeff Ello[^]
