Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Query ceation

Hi you experts. An example of the data in the table is :

id               PeopleId             Number              Version
----------------------------------------------------------------
1               100                        201                  1  
2               100                        201                  2
3               100                        201                  3
4               100                        205                  1

I want a query that by providing only the PeopleId as parameter to be able to give as result the followiing rows:

id               PeopleId             Number              Version
----------------------------------------------------------------
3               100                        201                  3
4               100                        205                  1

I want to be able to get only the top version of a number.
Thank you all in advance.
0
adktd
Asked:
adktd
  • 2
  • 2
1 Solution
 
HilaireCommented:
please try

select id, PeopleId, Number, Version
from <YourTable> a
where id = (select top 1 id from <YourTable> where Number = a.Number order by Version desc)
and PeopleId = 100
0
 
adktdAuthor Commented:
i tried it .I only get one row.
id              PeopleId             Number              Version
----------------------------------------------------------------
3               100                        201                  3
0
 
HilaireCommented:
please try

select id, PeopleId, Number, Version
from <YourTable> a
where id = (select top 1 id from <YourTable> where Number = a.Number and PeopleId = a.PeopleId order by Version desc)
and PeopleId = 100
0
 
adktdAuthor Commented:
Thank you it works great now
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now