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

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

Need to return the latest updated row

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
knamc
Asked:
knamc
  • 2
1 Solution
 
chapmandewCommented:
select * from
(
select *, ranking = dense_rank() over(partition by serverid order by DecomDate desc)
from tablename
) where ranking = 1
0
 
knamcAuthor Commented:
Doesn't like the where clause
0
 
chapmandewCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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