GROUP problems - 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

General SQL Server Forums

New to SQL Server Programming

GROUP problems

Author

Topic

EHJ
Starting Member

2 Posts

Posted - 2010-10-01 : 18:10:47

Hi.
I have one table with 3 main columns and need to select last record „strUserName“ „dtTime“ „nFlags“
If I select user and defaultnaem2 it shows one line per user. When I add the flags it shows two rows one with „0“and one with „1“
I need to have the last one „0“or „1“

My script.

SELECT TOP (100) PERCENT strUserName, MAX(dtTime) AS dtTime
FROM dbo.IRReview
WHERE (nUserRecID > 0) AND (strDefaultName2 = N'D008') AND (DATEDIFF(day, GETDATE(), dtTime) = 0) AND (dtTime <= GETDATE())
GROUP BY strUserName
ORDER BY strUserName

with nFlags

SELECT TOP (100) PERCENT strUserName, dtTime, RIGHT(nFlags, 1) AS nFlags
FROM dbo.IRReview
GROUP BY nUserSiteCode, dtTime, strUserName, nUserRecID, strDefaultName2, RIGHT(nFlags, 1)
HAVING (nUserRecID > 0) AND (strDefaultName2 = N'D008') AND (DATEDIFF(day, GETDATE(), dtTime) = 0) AND (dtTime <= GETDATE())
ORDER BY dtTime DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-02 : 02:52:31

[code]
SELECT r.strUserName, r.dtTime, RIGHT(r.nFlags, 1) AS nFlags
FROM dbo.IRReview r
JOIN
(
SELECT strUserName, MAX(dtTime) AS dtTime
FROM dbo.IRReview
WHERE (nUserRecID > 0) AND (strDefaultName2 = N'D008') AND (DATEDIFF(day, GETDATE(), dtTime) = 0) AND (dtTime <= GETDATE())
GROUP BY strUserName
)r1
ON r1.strUserName = r.strUserName
AND r1.dtTime = r.dtTime
ORDER BY r.strUserName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

EHJ
Starting Member

2 Posts

Posted - 2010-10-02 : 04:19:30

yes works

I tried similar to this but did not use two

r1.strUserName = r.strUserName
AND r1.dtTime = r.dtTime

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 09:35:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources