simple query - SQL Server Forums

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)

simple query

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2007-12-06 : 16:14:36

hi , I have table like below

TrainID UserID MsgType User Data CreatedDate (dd/mm/yy)
1111 1 SystemAinfo xtgdttggt 01/10/07
1112 1 SystemBinfo xtgsaadggt 04/10/07
1113 1 SystemCinfo xtgdsatggt 02/10/07
1114 2 SystemAinfo xtgdstggt 08/10/07
1115 2 SystemBinfo xtgdstggt 11/10/07
1116 2 SystemCinfo xtgdstggt 10/10/07
1117 1 SystemAinfo xtgdstggt 12/10/07
1118 1 SystemBinfo xtgdstggt 13/10/07
1119 2 SystemCinfo xtgdstggt 15/10/07

user have 3 types of messages (SystemAinfo,SystemBinfo,SystemAinfo)
for a 'particular user' a 'particular message' can appear number of times but with different createdDate (column)

i want a query to retrive UserData (column) value for SystemBinfo messageType that have maximum createDate for a particular user

from the above for userid 1 i need the below data
1118 1 SystemBinfo xtgdstggt 13/10/07

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 16:26:55

[code]DECLARE @Sample TABLE (TrainID INT, UserID INT, MsgType VARCHAR(20), UserData VARCHAR(20), CreatedDate DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1111, 1, 'SystemAinfo', 'xtgdttggt', '01/10/07' UNION ALL
SELECT 1112, 1, 'SystemBinfo', 'xtgsaadggt', '04/10/07' UNION ALL
SELECT 1113, 1, 'SystemCinfo', 'xtgdsatggt', '02/10/07' UNION ALL
SELECT 1114, 2, 'SystemAinfo', 'xtgdstggt', '08/10/07' UNION ALL
SELECT 1115, 2, 'SystemBinfo', 'xtgdstggt', '11/10/07' UNION ALL
SELECT 1116, 2, 'SystemCinfo', 'xtgdstggt', '10/10/07' UNION ALL
SELECT 1117, 1, 'SystemAinfo', 'xtgdstggt', '12/10/07' UNION ALL
SELECT 1118, 1, 'SystemBinfo', 'xtgdstggt', '13/10/07' UNION ALL
SELECT 1119, 2, 'SystemCinfo', 'xtgdstggt', '15/10/07'

SELECT TrainID,
UserID,
UserData,
CreatedDate
FROM (
SELECT TrainID,
UserID,
UserData,
CreatedDate,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreatedDate DESC) AS RowID
FROM @Sample
WHERE MsgType = 'SystemBinfo'
) AS d
WHERE RowID = 1
ORDER BY UserID[/code]

E 12°55'05.25"
N 56°04'39.16"

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2007-12-07 : 11:32:27

sorry for late Response,

I am getting the following error
'ROW_NUMBER' is not a recognized function name.

I am using sql server 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:36:11

Really?
You posted in the SQL Server 2005 forum...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:38:37

[code]DECLARE @Sample TABLE (TrainID INT, UserID INT, MsgType VARCHAR(20), UserData VARCHAR(20), CreatedDate DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1111, 1, 'SystemAinfo', 'xtgdttggt', '01/10/07' UNION ALL
SELECT 1112, 1, 'SystemBinfo', 'xtgsaadggt', '04/10/07' UNION ALL
SELECT 1113, 1, 'SystemCinfo', 'xtgdsatggt', '02/10/07' UNION ALL
SELECT 1114, 2, 'SystemAinfo', 'xtgdstggt', '08/10/07' UNION ALL
SELECT 1115, 2, 'SystemBinfo', 'xtgdstggt', '11/10/07' UNION ALL
SELECT 1116, 2, 'SystemCinfo', 'xtgdstggt', '10/10/07' UNION ALL
SELECT 1117, 1, 'SystemAinfo', 'xtgdstggt', '12/10/07' UNION ALL
SELECT 1118, 1, 'SystemBinfo', 'xtgdstggt', '13/10/07' UNION ALL
SELECT 1119, 2, 'SystemCinfo', 'xtgdstggt', '15/10/07'

SELECT s.TrainID,
s.UserID,
s.MsgType,
s.UserData,
s.CreatedDate
FROM @Sample AS s
INNER JOIN (
SELECT UserID,
MAX(CreatedDate) AS mcd
FROM @Sample
WHERE MsgType = 'SystemBinfo'
GROUP BY UserID
) AS p ON p.UserID = s.UserID
WHERE p.mcd = s.CreatedDate[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources