MSSQL - How to count records & select max timestamp?

Posted on 2009-02-12
Last Modified: 2012-05-06
I've got a table that looks like this:

Id | Uid | Value | Timestamp
1 | 65 | B | 2009-01-01 12:01:00.100
2 | 65 | B | 2009-01-02 12:01:00.100
3 | 65 | B | 2009-01-03 12:01:00.100
4 | 65 | B | 2009-01-04 12:01:00.100
5 | 65 | B | 2009-01-05 12:01:00.100

I'm trying to count the records that have the same Uid and Value, while returning the Uid, Value, and maximum timestamp like this:

Id | Uid | Value | Timestamp | counter
1 | 65 | B | 2009-01-05 12:01:00.100 | 5

I keep running into group by errors.  Is there a way to do this?
Question by:jen_jen_jen
    LVL 60

    Accepted Solution

    try this:

    select id = min(id), uid, value, max(timestamp),counter = count(*)
    from tablename
    group by uid, value
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello jen_jen_jen,

    SELECT MIN(Id) AS Id, Uid, Value, MAX(Timestamp) AS Timestamp, COUNT(*) AS Counter
    FROM SomeTable
    GROUP BY Uid, Value


    LVL 92

    Expert Comment

    by:Patrick Matthews
    Guess I need more coffee :)
    LVL 37

    Expert Comment

    if you are using sql 2005 then

    select id, uid, value, max(timestamp) over (partition by id, uid) as timestamp, count(*) over (partition by id, uid) as counter
    from your table
    LVL 68

    Expert Comment

    MIN(id) will not work -- you need the id that corresponds to the rwo with the MAX(timestamp), not just the lowest id in the table.

    Author Closing Comment

    Thanks! Never would have figured that out....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now