SQL : Get the max elements in a join?

Question

Possible Duplicate:
Fetch the row which has the Max value for a column

I have two tables

1. A table USER with a USER_ID, NAME, ...
2. A table STATUS with a STATUS_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 easely get all my users with all they status. BUT I would like to create a query to retriev all my users with they last status only. Is it possible ? What shoul I use ?

for the efficiency and simplicity i would suggest to have only 1 status in the table and to keep the history in the separate table (i.e `STATUS_HISTORY`). This means you have to eiither use triggers or do 2 queries on update/insert — Imre L, Aug 21 '12 at 14:59
@davidmontoyago I'm not a DBA. I tried and I'm still looking for a solition on the web. — Bastien Vandamme, Aug 21 '12 at 15:10
@Gordon Linoff Why do you want to know the database I use? I'm looking for a standar SQL solution. — Bastien Vandamme, Aug 21 '12 at 15:13
@Imre L I don't have the power to modify the schema of the database. — Bastien Vandamme, Aug 21 '12 at 15:14
@Magnus Yes, possible but I didnt find it with my search criteria. — Bastien Vandamme, Aug 21 '12 at 15:14
@TimSchmelter The answers in that thread are SQL server 2005/Oracle compatible and some are general SQL compatible. — Magnus, Aug 21 '12 at 15:14
@DranDane we want to know the database you are using because some SQL syntax is database specific. — Magnus, Aug 21 '12 at 15:16
Try a subquery to filter out the status, then do a join of status to user. — CBredlow, Aug 21 '12 at 14:55

score 0 · Answer 1 · edited Aug 21 '12 at 15:20

0

Join like this:

SELECT *
FROM USER   U
JOIN STATUS S ON U.USER_ID = S.USER_ID
              AND NOT EXISTS (
                SELECT 1 
                FROM   STATUS S2
                WHERE  S2.USER_ID = S.USER_ID 
                AND    S2.START_DATE > S.START_DATE
              )

This will only join those status records for which there doesn't exist another status for the same user and with a more recent date

edited Aug 21 '12 at 15:20

Bastien Vandamme

17,659
30
118
200

answered Aug 21 '12 at 14:56

Lukas Eder

211,314
129
689
1,509

I edited my question and your answer – Bastien Vandamme Aug 21 '12 at 15:20

score 0 · Answer 2 · answered Aug 21 '12 at 14:57

0

try

select * from user 
inner join 
(
   select * from status
   inner join 
      (select max(Date_start) as maxdate, status_id as id  
           from status group by Status_id)   d
   on status_id = d.id and Date_start = d.maxdate
)d1 
on userid =d1.People_fK

answered Aug 21 '12 at 14:57

Pranay Rana

175,020
35
237
263

@DranDane - is that work for you ... – Pranay Rana Aug 21 '12 at 15:57

score 0 · Answer 3 · edited Aug 21 '12 at 15:26

0

I assume that you're using SQL-Server :

You can use a cte with ROW_NUMBER function:

WITH CTE AS
(
    SELECT U.USER_ID, U.NAME, U.DATE_START, S.STATUS
    ,   RN = ROW_NUMBER()OVER(PARTITION BY S.STATUS_ID ORDER BY U.DATE_START DESC)
    FROM USER U INNER JOIN STATUS S ON U.User_ID=S.PEOPLE_FK
)
SELECT * FROM CTE WHERE RN = 1

edited Aug 21 '12 at 15:26

Bastien Vandamme

17,659
30
118
200

answered Aug 21 '12 at 14:59

Tim Schmelter

450,073
74
686
939

score 0 · Answer 4 · answered Aug 21 '12 at 14:59

0

select * from 
    user a, 
    status b 
where 
      a.user_id = b.poeple_id 
  and b.start_date = (select max(start_date) 
                             from status c 
                             where c.people_id=b.people_id)

answered Aug 21 '12 at 14:59

Ahmad

12,336
6
48
88

SQL : Get the max elements in a join?

4 Answers4