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
 HOW TO SELECT LAST ROWS IN GROUP BY CLAUSE

Author  Topic 

mubash3r
Starting Member

2 Posts

Posted - 2008-11-07 : 02:17:27
I have a table which stores datewise Transactions of different items. Fields and sample data is
RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

Please tell me if someone knows the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 02:19:08
[code]SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY Date DESC) AS Seq,*
FROM Table
)t
WHERE Seq=1[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 02:20:33
And if sql 2000 or earlier

SELECT t.*
FROM Table t
INNER JOIN (SELECT ItemID,MAX(date) AS latest
FROM Table
GROUP BY ItemID) t1
ON t1.ItemID=t.ItemID
AND t1.latest=t.Date
Go to Top of Page

mubash3r
Starting Member

2 Posts

Posted - 2008-11-07 : 02:59:15
Thanks visakh16.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 03:00:38
Welcome
Go to Top of Page

huub
Starting Member

4 Posts

Posted - 2014-10-17 : 10:20:43
How can I join table Living_Situation with table customer in 1 query?
The join field is Custid.

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
FROM Living_Situation
)t
WHERE Seq=1

SELECT TOP 1000 [custid], [surname]
FROM customer
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-10-25 : 15:51:20
quote:
Originally posted by huub

How can I join table Living_Situation with table customer in 1 query?
The join field is Custid.

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
FROM Living_Situation l
INNER JOIN Customer c
ON c.CustId = l.Custid

)t
WHERE Seq=1




like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -