Solved

Need to return the latest updated row

Posted on 2008-10-28
3
198 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 250 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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