4

I have two tables History and Historyvalues:

History

HID(uniqeidentifier) | Version(int)
a1 | 1
a2 | 2
a3 | 3
a4 | 4

Historyvalues

HVID(uniqeidentifier) | HID(uniqeidentifier) | ControlID(uniqeidentifier) | Value(string) 
b1 | a1 | c1 | value1 
b2 | a2 | c1 | value2 
b3 | a2 | c2 | value3

Now I Need a query where I can get a list with the last historyvalue of each control from a specific Version like:

Get the last values from Version 3 -> receiving ->

HVID | ControlID | Value
b2 | c1 | value2
b3 | c2 | value3

I tried something like this:

Select HVID, ControlId, max(Version), Value from 
(   
    Select HVID, ControlId, Version, Value
    from History inner JOIN
         Historyvalues ON History.HID = Historyvalues.HID
    where Version <= 3  
) as a
group by ControlId
order by Version desc

but this does not work.

Are there any ideas?

Thank you very much for your help.

Best regards

Improve this question
2
  • Should Historyvalues ON History.HID = Historyvalues.HVID be HID not HVID ? Commented Aug 29, 2013 at 8:46
  • Yes of course you're right, sorry about this. Now it's correct but of course it's still not working. Commented Aug 29, 2013 at 9:29

2 Answers 2

Reset to default
1

Latest version from each control with your specific Version (WHERE t1.Version <= 3)

Query:

SQLFIDDLEExample

SELECT HVID, ControlId, Version, Value 
FROM
(   
    SELECT t2.HVID, t2.ControlId, t1.Version, t2.Value,
           ROW_NUMBER() OVER(PARTITION BY t2.ControlId ORDER BY t1.Version DESC) as rnk
    FROM History t1 
     JOIN Historyvalues t2 
      ON t1.HID = t2.HID
    WHERE t1.Version <= 3  
) AS a
WHERE a.rnk = 1
ORDER BY a.Version desc

Result:

| HVID | CONTROLID | VERSION |  VALUE |
|------|-----------|---------|--------|
|   b2 |        c1 |       2 | value2 |
|   b3 |        c2 |       2 | value3 |
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

here is your solution

 Select  Historyvalues.HVID,Historyvalues.ControlID,Historyvalues.Value
 from Historyvalues
 inner join History on Historyvalues.hid=History.hid
 where Historyvalues.hvid in (
            select MAX(Historyvalues.hvid) from Historyvalues
        inner join History on Historyvalues.hid=History.hid
        group by ControlID)
Improve this answer

5 Comments

Unfortunately this does not work because it Returns only the controlId's and values from the one history HID. If you can see in example above, Version 3 (a3) has no controls. Version 2(a2) has two ControlId's c1 and c2 but Version1(a1) has the controlId c1 as well. So I Need only thecontrolId's and values for the c1 which has the highest historyversion. So if I ask for Version 3, there is the controlid c2 in it (b3) and the higher Version of c1 and this is the b2.
Like this, I receive the error "The objects "History" and "History" in the FROM clause have the same exposed names. Use correlation names to distinguish them". Could you please help for this again? Thanks a lot.
Like this, the Version is not relevant anymore. Are you sure with the max(Historyvalues.hvid) because the hvid is a guid? Where is the Version? I Need to ask give me the result for the Version 3 for example. Do you have any idea?
you just need to keep in mind that your latest version of control to be inserted at last
Is the max(Historyvalues.hvid) not sorting the id? Is the database itself looking for an insertdate? I don't understand this. If I need the query only for Version 3 and smaller (not Version 4,5,6 etc) is this work like this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.