CODEWITHSUNDEEP
sqlms-access
user1137370
user1137370

Reputation: 137

SQL JOIN same table

I have one table with "ID", "Sequence", "Status":

    ID | Seq | Status
    ======================
    10 | 001 | 010
    10 | 002 | test
    10 | 003 | 005
    11 | 001 | 010
    11 | 002 | 338

The result from my query should give me the complete table plus an extra column with the status for the highest sequence for the respective ID:

    ID | Seq | Status | LStatus
    ======================
    10 | 001 | 010    | 005
    10 | 002 | test   | 005
    10 | 003 | 005    | 005
    11 | 001 | 010    | 338
    11 | 002 | 338    | 338

I have no clue how to do it. I startet with something like that:

SELECT a.*, b.status as lstatus
FROM table a
left join (select top 1 b.status from table b order by b.seq DESC) 
on a.id = b.id

Hope you can help me :)

Thanks in advance!!!

Upvotes: 2

Views: 55

Answers (4)

Yogesh.Sharma
Yogesh.Sharma

Reputation: 1

Use can use subquery here:

select *, 
       (select top 1 Status from table where id = t.id order by status desc) as Lstatus 
from table t;

Upvotes: 0

SQLChao
SQLChao

Reputation: 7837

You can try using ROW_NUMBER() to assign number to each row within the ids ordered by seq. Join that back to your table where the id matches and the rownumber rn = 1.

DEMO

SELECT 
  a.*
, b.status
FROM table a
JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq DESC) AS rn
  FROM dbo.Table) b ON a.id = b.id AND b.rn = 1

Upvotes: 0

Srinath S
Srinath S

Reputation: 23

Try the below.

with status as (
Select distinct(id),status from table order by seq desc)select a.*,s.status as LStatus from table a,status s where a.id=s.id;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You should use a group by max for the subquery and join the base table

  SELECT a.*, b.status as t.lstatus
  FROM my_table a
  INNER  join (select id,   
            max(b.status) lstatus 
            from my_table b 

              group by id)  t on t.id = a.id and

for numeric value only 

  SELECT a.*, b.status as t.lstatus
  FROM my_table a
  INNER  join (select id,   
            max(b.status) lstatus 
            from my_table b 
            where IsNumeric([b.status])=True
              group by id)  t on t.id = a.id and

Upvotes: 3

Related Questions