0

Hi I'm trying to get a unique list of ID values based on the latest (max) version number

My table looks like:

id  Version    Cost      Name     Status
---|-----|------------|--------|---------
35 | 1.0 | 200000     | john   | Open
36 | 1.0 | 400000     | juliet | Open
35 | 2.0 | 350000     | borat  | Closed
36 | 1.5 | 30000      | john   | Waiting Update

I want it to be able to return

id  Version    Cost      Name     Status
---|-----|------------|--------|---------
35 | 2.0 | 350000     | borat  | Closed
36 | 1.5 | 30000      | john   | Waiting Update

I've tried using this but can't get it to work and I'm not sure where I'm going wrong

select FB.* from CSLL.Feedback FB

inner join (
    select ID
    ,max(Version)
    ,Status as MaxID
    from CSLL.Feedback
    group by ID
) groupedID

ON FB.ID = groupedID.ID
AND FB.Version = groupedID.MaxID
and FB.Status = groupedID.Status

Which was based on a response to another question found here

LINKY

Can anyone help at all?

Many thanks in advance

Tom

Community
  • 1
  • 1
Tom
  • 9,725
  • 3
  • 31
  • 48
  • Solved it with this in case anyone is interested: " select ID, max(Version), Status from CSLL.Feedback Group By ID, Status " If there is a better way of doing it please say – Tom Jul 14 '14 at 12:36
  • Yes. The version you had before was better, but this bit was (obviously?) wrong: "AND FB.Version = groupedID.MaxID"... and so (potentially) was this bit " ,max(Version),Status as MaxID". Look again at the examples provided. – Strawberry Jul 14 '14 at 12:50
  • @Strawberry It all seems fine there other then my bad name calling (have renamed MaxID to MaxVersion). After removing the parts referencing the status column it works fine. Any thoughts to getting this included as well? Many Thanks, – Tom Jul 14 '14 at 14:45
  • @Strawberry - Spoke to soon. It is now returning everything `select FB.* from CSLL.Feedback FB inner join ( select ID ,max(Version) as MaxVersion from CSLL.Feedback group by ID ) groupedID ON FB.ID = groupedID.ID AND FB.Version = groupedID.MaxVersion` – Tom Jul 14 '14 at 14:49
  • Looks good to me. Perhaps your error lies elsewhere. – Strawberry Jul 14 '14 at 15:51

1 Answers1

0

Try this.

select fb1.* from Feedback fb1 
LEFT JOIN Feedback fb2 on fb2.id=fb1.id and fb2.version>fb1.version
where fb2.id IS NULL;
Charvee Shah
  • 730
  • 1
  • 6
  • 21