?
Solved

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

Posted on 2014-11-14
1
Medium Priority
?
161 Views
Last Modified: 2014-11-14
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
Comment
Question by:cleaclev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40442307
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question