We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

MSSQL - How to count records & select max timestamp?

Medium Priority
2,031 Views
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?
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
try this:

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2010

Commented:
Hello jen_jen_jen,

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

Regards,

Patrick
CERTIFIED EXPERT
Top Expert 2010

Commented:
Guess I need more coffee :)
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Commented:
Thanks! Never would have figured that out....
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.