Go Premium for a chance to win a PS4. Enter to Win

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

SQL - How do I get rows with highest value in one column

I have one table with all contracts for cars. One car can have more contracts. For example one contract from 1 januari 2013 until 1 july 2013 and a next contract from 1 sept 2013 until 2 febr 2014.
How can I make a view in SQL 2000 server that shows the last contract of each car.

Example:
number  -  carnumber  -  enddate
123          -  195               -  20130701
158          -  155              -   20141012
168          -  195               -  20140201

As result I want
number  -  carnumber  -  enddate
158          -  155              -   20141012
168          -  195               -  20140301
0
cleaclev
Asked:
cleaclev
1 Solution
 
Phillip BurtonCommented:
select (select number from mytable M1 where M.carnumber = M1.carnumber and max(M.enddate) = M1.enddate) as number, carnumber, max(enddate) as enddate
from mytable M
group by carnumber
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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