?
Solved

Need to return the latest updated row

Posted on 2008-10-28
3
Medium Priority
?
207 Views
Last Modified: 2012-05-05
I have entries in a table as:

ServerID      DecomDate      DecomNumber      Comments      DateChanged      ChangedBy
81      8/6/08 20:07      na      Comment Entry      10/28/2008      USER1
140      9/15/08 18:00      1812040      Comment Entry      10/28/2008      USER1
295      4/8/08 3:00      1634454      Comment Entry      10/28/2008      USER1
435      5/31/08 0:00      1591700      Comment Entry      10/28/2008      USER1
3943      6/26/08 13:20      1777151      Comment Entry      10/28/2008      USER1
3943      6/29/08 14:37      na      Comment Entry      10/28/2008      USER1
3943      6/29/08 14:37      na      Comment Entry      10/28/2008      USER1
4319      6/4/08 13:12      N/A      Comment Entry      10/28/2008      USER1
4319      6/4/08 13:12      N/A      Comment Entry      10/28/2008      USER1
4342      6/4/08 13:24      N/A      Comment Entry      10/28/2008      USER1
4342      6/4/08 13:24      N/A      Comment Entry      10/28/2008      USER1
4348      6/4/08 13:36       N/A      Comment Entry      10/28/2008      USER1
4348      6/4/08 13:36      N/A      Comment Entry      10/28/2008      USER1
4444      6/4/08 13:52      N/A      Comment Entry      10/28/2008      USER1
4444      6/4/08 13:52       N/A      Comment Entry      10/28/2008      USER1
6536      3/14/08 10:05      1618971      Comment Entry      10/28/2008      USER1
6536      3/17/08 11:13      1589044      Comment Entry      10/28/2008      USER1
6575      3/13/08 9:55      1618989      Comment Entry      10/28/2008      USER1
6575      3/13/08 10:05      1618883      Comment Entry      10/28/2008      USER1
9359      5/23/08 12:00      1705042      Comment Entry      10/28/2008      USER1
9359      5/28/08 9:07      1705042      Comment Entry      10/28/2008      USER1
9359      5/28/08 9:07      1705042      Comment Entry      10/28/2008      USER1
9704      4/21/08 13:37      1462240      Comment Entry      10/28/2008      USER1
9704      5/8/08 11:42      1462240      Comment Entry      10/28/2008      USER1
13841      5/15/08 16:56      na      Comment Entry      10/28/2008      USER1
13841      5/15/08 16:59      na      Comment Entry      10/28/2008      USER1

I need to return all rows, and only one row when the row count > 1, If the row count is >1 I want to return the MAX(DecomDate).

I only want one entry in the Query for each ID.
0
Comment
Question by:knamc
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 22825587
select * from
(
select *, ranking = dense_rank() over(partition by serverid order by DecomDate desc)
from tablename
) where ranking = 1
0
 

Author Comment

by:knamc
ID: 22825900
Doesn't like the where clause
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22825909
thats because I goofed it up...try this.  :)

select * from
(
select *, ranking = dense_rank() over(partition by serverid order by DecomDate desc)
from tablename
) a
where ranking = 1
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

594 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