0

i am trying to retrieve data from tables and my script looks like this->

SELECT 
            e.*, 
            c.ClientName 
        FROM 
            Entities e WITH(nolock) 
            JOIN Clients c WITH(nolock) ON c.ClientId = e.ClientId 
        WHERE 
            e.ClientId = 222222

part of output is->

entityid                   clientid                 version                clientname

1                           222222                   1                     first
2                           222222                    1                    second

what i need to get is for this clientid, fetch all entities with their latest version.

what i have written->

SELECT 
            e.*, 
            c.ClientName 
        FROM 
            Entities e WITH(nolock) 
            JOIN Clients c WITH(nolock) ON c.ClientId = e.ClientId 
        WHERE 
            e.[Version] = (SELECT MAX([Version]) FROM Entities b WHERE e.EntityId=b.EntityId)  AND

            e.ClientId = 222222

what this gives me->

   entityid                   clientid                 version                clientname

1                           222222                   1                     first

which is wrong. can someone help?

techbuster99
  • 1
  • 3
  • 1
    Is there a *good* reason you are using `NOLOCK` here? – Thom A May 27 '22 at 10:49
  • Presumably there is another row in your table `Entities` where `entityid` has a value of `2` and `version` has a value greater than `1`; perhaps for a different `clientid`. – Thom A May 27 '22 at 10:50
  • yes true. so what do i do? – techbuster99 May 27 '22 at 10:55
  • 1
    Correlate your subquery on the `clientid` too? There's too little information for me to guess; especially as there's no expected results and minimal sample data. – Thom A May 27 '22 at 11:04
  • pls help dude. it is very important. just guess and i will try to see if it works – techbuster99 May 27 '22 at 11:05
  • 2
    [so] isn't for guesses... If it's important, improve the question so that users here can help. Also, please don't call me "dude". – Thom A May 27 '22 at 11:08
  • 1
    FYI - not everyone is a "dude" – SMor May 27 '22 at 11:11
  • @SMor i cant figure out how to use it in mine. a little help please. – techbuster99 May 27 '22 at 11:17
  • 1
    What they are trying to tell you is that if you want help, you need to help us first. We need more information before we can help. Providing us with sample data and expected result would really help us, until we have that it is very difficult for anyone to help you. – GuidoG May 27 '22 at 11:39
  • i completely understand and next time i will take care of this. for now this issue has been solved @GuidoG – techbuster99 May 27 '22 at 12:13
  • If this is no longer an issue, then please delete this question. Thank you – GuidoG May 27 '22 at 12:26

0 Answers0