Group By / Max. Not working with primary keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By / Max. Not working with primary keys

Author

Topic

rternier
Starting Member

44 Posts

Posted - 2007-10-10 : 12:54:20

I'm having trouble trying to get this SQL Query Working. I am looking to select the MAX TimeStamp (the newest one) for each user.

Now this sample data below only has 3 users, however some DataBases might have 1 user, othes might have 500.

Any help would rock.

I have the following Data:

intDataID - PK
intUserID - FK


Time Stamp              intUserID   intDataID
----------------------- ----------- ------------
2007-10-05 08:55:40.000 1           1
2007-10-05 08:55:40.000 2           2
2007-10-05 08:55:40.000 3           3
2007-10-05 08:55:45.000 1           4
2007-10-05 08:55:45.000 2           5
2007-10-05 08:55:45.000 3           6
2007-10-05 08:55:50.000 1           7
2007-10-05 08:55:50.000 2           8
2007-10-05 08:55:50.000 3           9
2007-10-05 08:55:55.000 1           10
2007-10-05 08:55:55.000 2           11
2007-10-05 08:55:55.000 3           12

And I'm looking to get it like this:


Time Stamp              intUserID
----------------------- -----------
2007-10-05 08:55:55.000 1
2007-10-05 08:55:55.000 2
2007-10-05 08:55:55.000 3

But I need the intDataID column as well. Any help would be great.
I cannot do MAX(intDataID) in the Query because a computer might lag a bit before updating this Table. So I need to do this fromt he TimeStamps Perspective.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 13:03:50

read this:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

you requirements are not fully defined. It makes no logical sense to return intDataID as well without more info.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 13:10:48

[code]
DECLARE @TestData TABLE
(
[Time Stamp] datetime,
intUserID int,
intDataID int
)

INSERT INTO @TestData
SELECT '20071005 08:55:40.000', 1, 1 UNION ALL
SELECT '20071005 08:55:40.000', 2, 2 UNION ALL
SELECT '20071005 08:55:40.000', 3, 3 UNION ALL
SELECT '20071005 08:55:45.000', 1, 4 UNION ALL
SELECT '20071005 08:55:45.000', 2, 5 UNION ALL
SELECT '20071005 08:55:45.000', 3, 6 UNION ALL
SELECT '20071005 08:55:50.000', 1, 7 UNION ALL
SELECT '20071005 08:55:50.000', 2, 8 UNION ALL
SELECT '20071005 08:55:50.000', 3, 9 UNION ALL
SELECT '20071005 08:55:55.000', 1, 10 UNION ALL
SELECT '20071005 08:55:55.000', 2, 11 UNION ALL
SELECT '20071005 08:55:55.000', 3, 12

SELECT X.[MAX_Time_Stamp], T.intUserID, T.intDataID
FROM
(
SELECT [MAX_Time_Stamp] = MAX([Time Stamp]), intUserID
FROM @TestData
GROUP BY intUserID
) AS X
JOIN @TestData AS T
ON T.intUserID = X.intUserID
AND T.[Time Stamp] = X.[MAX_Time_Stamp]
[/code]
Kristen

rternier
Starting Member

44 Posts

Posted - 2007-10-10 : 13:12:08

Hey jeff,

The reason I need that ID is for modifications on the webform so my application can link back to this, and then work with other tables.

if I can solve how to do this with the intDataId, I can add other columns to this as well (which I cannot put down because of NDA's unfortunately).

FOr requirements:

I need a query that will look at a table that will be updated once every 0.1 seconds. I need a very accurate time shot of this table that takes the latest data (based on TimeStamp) for all users in the system and return a resultset that contains that data.

I could run this query:

SELECT MAX(datTimeStamp) AS 'Time Stamp', intUserID FROM [tblData] GROUP BY intUserID

which gives me the last table... but I need the intDataID in that result set that correspond with the TimeStamp and the intUserID.

I tried:

SELECT MAX(datTimeStamp) AS 'Time Stamp', intUserID,intDataID FROM [tblData] GROUP BY intUserID, intDataID

but that gives me the first set with ALL records, where I only wanted the last 3 from that result. I can't use MAX(intDataID) as the timestamp might be older than the highest Id (A client updating this table might've lagged a bit so the PK will be higher but the Timestamp will be lower...)

I hope that gives you more information.

rternier
Starting Member

44 Posts

Posted - 2007-10-10 : 13:15:33

Kristin, That works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources