Limit to only one row per session

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)

Limit to only one row per session

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-02 : 19:17:01

I have the following and would like the code to limit the result set to only one row per SessionID, The row should be the one with the lowest action time.

Currently it is returning this example when I need in this case rows 1 and 2 because row three is the second Select Site case for the same SessionID:

LoginName ActionType ActionTime, UserID, SessionID, SessionStartTime, UserResponseinSeconds
HJH Select Site 2008-10-07 00:00:00.000 3 21 2008-01-07 00:00:00.000 23673600
SJH Select Site 2009-01-01 00:01:00.000 1 24 2009-01-01 00:00:00.000 60
HJH Select Site 2009-01-01 00:01:40.000 3 24 2009-01-01 00:00:00.000 100

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Users_Response] Script Date: 11/03/2008 09:29:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Users_Response]

@DBName nvarchar (max)

AS
SET NOCOUNT ON

SELECT LoginName, ActionType, ActionTime, UserID, SessionID, SessionStartTime, DATEDIFF(second, SessionStartTime, ActionTime) AS UserResponseinSeconds
FROM (SELECT UserDetails.LoginName, OperatorAction.ActionType, MIN(OperatorAction.ActionTime) AS ActionTime, OperatorAction.UserID, SessionDetails.SessionID, SessionDetails.SessionStartTime
FROM UserDetails INNER JOIN
OperatorAction ON UserDetails.UserID = OperatorAction.UserID INNER JOIN
SessionDetails ON OperatorAction.SessionID = SessionDetails.SessionID
AND UserDetails.IsDeleted = 'False'
AND SessionDetails.SourceType = 'Transmitter'
AND OperatorAction.ActionType = 'Select Site'
GROUP BY SessionDetails.SessionID, OperatorAction.ActionType, UserDetails.LoginName, OperatorAction.UserID, SessionDetails.SessionStartTime) AS E
ORDER BY SessionStartTime, ActionTime

SET NOCOUNT OFF

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-03 : 00:04:13

I found the answer with the use of Row Number,Over, Partition:

SELECT LoginName, ActionType, ActionTime, UserID, SessionID, SessionStartTime, SessionEndTime, DATEDIFF(second, SessionStartTime, ActionTime) AS UserResponseinSeconds, PARSession
FROM (SELECT LoginName, ActionType, ActionTime, OperatorAction.UserID, SessionDetails.SessionID, SessionStartTime, SessionEndTime, ROW_NUMBER() Over (Partition by SessionDetails.SessionID Order by ActionTime) AS PARSession
FROM UserDetails INNER JOIN
OperatorAction ON UserDetails.UserID = OperatorAction.UserID INNER JOIN
SessionDetails ON OperatorAction.SessionID = SessionDetails.SessionID
WHERE SessionStartTime >= @StartDateRange AND SessionStartTime <= @EndDateRange
AND IsDeleted = 'False'
AND SourceType = 'Transmitter'
AND ActionType = 'Select Site'
GROUP BY SessionDetails.SessionID, ActionType, ActionTime, LoginName, OperatorAction.UserID, SessionStartTime, SessionEndTime) AS E
WHERE E.PARSession = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 00:07:20

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SessionID ORDER BY ActionTime) AS Seq,LoginName, ActionType, ActionTime, UserID, SessionID, SessionStartTime, DATEDIFF(second, SessionStartTime, ActionTime) AS UserResponseinSeconds
FROM
(SELECT UserDetails.LoginName, OperatorAction.ActionType, MIN(OperatorAction.ActionTime) AS ActionTime, OperatorAction.UserID, SessionDetails.SessionID, SessionDetails.SessionStartTime
FROM UserDetails INNER JOIN
OperatorAction ON UserDetails.UserID = OperatorAction.UserID INNER JOIN
SessionDetails ON OperatorAction.SessionID = SessionDetails.SessionID
AND UserDetails.IsDeleted = 'False'
AND SessionDetails.SourceType = 'Transmitter'
AND OperatorAction.ActionType = 'Select Site'
GROUP BY SessionDetails.SessionID, OperatorAction.ActionType, UserDetails.LoginName, OperatorAction.UserID, SessionDetails.SessionStartTime
) AS E
)r
WHERE Seq=1
ORDER BY SessionStartTime, ActionTime[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 00:08:12

ah...i didnt refresh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources