Getting Maximum Value Rows - 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)

Getting Maximum Value Rows

Author

Topic

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-12-02 : 00:54:19

The table is somewhat like this
Create Table #tab1(
[HistoryID] [int] IDENTITY(1,1),
[IssueID] [int] NULL,
[UserID] [int] NULL,
[CreatedDate] [datetime] NULL,

)
Insert #Tab1 values(1,3,'2008-09-24 18:13:56.000')
Insert #Tab1 values(1,3,'2008-09-25 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-26 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-22 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-28 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-29 18:13:56.000')
select * from #Tab1
will display
HistoryId IssueID UserID CreatedDate
1 1 3 2008-09-24 18:13:56.000
2 1 3 2008-09-25 18:13:56.000
3 2 4 2008-09-26 18:13:56.000
4 2 4 2008-09-22 18:13:56.000
5 2 4 2008-09-28 18:13:56.000
6 2 4 2008-09-29 18:13:56.000

i want all the rows with largest createddate group by IssueId and UserID..ie..
i want one row each for each same issueid and userid with largest createdDate
Please Help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:56:45

[code]SELECT [HistoryID],
[IssueID],
[UserID],
[CreatedDate]
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY UserID,IssueID ORDER BY CreatedDate DESC) AS Seq,*
FROM #tab1
)t
WHERE t.Seq=1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 01:01:03

just in case you're using sql 2000

SELECT t.[HistoryID],
t.[IssueID],
t.[UserID],
t.[CreatedDate] 
FROM #Tab1 t
INNER JOIN (SELECT IssueID,UserID,MAX(CreatedDate) AS Latest
            FROM #Tab1
            GROUP BY IssueID,UserID)t1
ON t1.IssueID =t.IssueID 
AND t1.UserID=t.UserID
AND t1.Latest=t.CreatedDate

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-12-02 : 01:08:06

quote:

Originally posted by visakh16

just in case you're using sql 2000

SELECT t.[HistoryID],
t.[IssueID],
t.[UserID],
t.[CreatedDate] 
FROM #Tab1 t
INNER JOIN (SELECT IssueID,UserID,MAX(CreatedDate) AS Latest
            FROM #Tab1
            GROUP BY IssueID,UserID)t1
ON t1.IssueID =t.IssueID 
AND t1.UserID=t.UserID
AND t1.Latest=t.CreatedDate

Hi Visakh which query will be better in sqlserver2005 beacuse the result will be used for another join

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 01:17:02

results from profiler

Create Table #tab1(
[HistoryID] [int] IDENTITY(1,1),
[IssueID] [int] NULL,
[UserID] [int] NULL,
[CreatedDate] [datetime] NULL,

)
Insert #Tab1 values(1,3,'2008-09-24 18:13:56.000')
Insert #Tab1 values(1,3,'2008-09-25 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-26 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-22 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-28 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-29 18:13:56.000')

--query 1
SELECT t.[HistoryID],
t.[IssueID],
t.[UserID],
t.[CreatedDate] 
FROM #Tab1 t
INNER JOIN (SELECT IssueID,UserID,MAX(CreatedDate) AS Latest
            FROM #Tab1
            GROUP BY IssueID,UserID)t1
ON t1.IssueID =t.IssueID 
AND t1.UserID=t.UserID
AND t1.Latest=t.CreatedDate


--query 2
SELECT [HistoryID],
[IssueID],
[UserID],
[CreatedDate]
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY UserID,IssueID ORDER BY CreatedDate DESC) AS Seq,*
FROM #tab1
)t
WHERE t.Seq=1

profiler results
-----------------------------------------

query      cpu    reads     writes     duration
-----------------------------------------------
query 1     16      168        2          69
query 2     16      168        4          22

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-12-02 : 01:37:38

quote:

Originally posted by visakh16

results from profiler

Create Table #tab1(
[HistoryID] [int] IDENTITY(1,1),
[IssueID] [int] NULL,
[UserID] [int] NULL,
[CreatedDate] [datetime] NULL,

)
Insert #Tab1 values(1,3,'2008-09-24 18:13:56.000')
Insert #Tab1 values(1,3,'2008-09-25 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-26 18:13:56.000')

Thanks a lot .....
Insert #Tab1 values(2,4,'2008-09-22 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-28 18:13:56.000')
Insert #Tab1 values(2,4,'2008-09-29 18:13:56.000')

--query 1
SELECT t.[HistoryID],
t.[IssueID],
t.[UserID],
t.[CreatedDate] 
FROM #Tab1 t
INNER JOIN (SELECT IssueID,UserID,MAX(CreatedDate) AS Latest
            FROM #Tab1
            GROUP BY IssueID,UserID)t1
ON t1.IssueID =t.IssueID 
AND t1.UserID=t.UserID
AND t1.Latest=t.CreatedDate


--query 2
SELECT [HistoryID],
[IssueID],
[UserID],
[CreatedDate]
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY UserID,IssueID ORDER BY CreatedDate DESC) AS Seq,*
FROM #tab1
)t
WHERE t.Seq=1

profiler results
-----------------------------------------

query      cpu    reads     writes     duration
-----------------------------------------------
query 1     16      168        2          69
query 2     16      168        4          22

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2008-12-02 : 01:39:08

Thanks a lot..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 01:54:13

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources