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.
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, Stock1, 5, 11-03-08, 10, 102, 5, 11-05-08, 3, 7*3, 8, 11-15-08, 25, 254, 8, 11-16-08, 8, 335, 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)tWHERE Seq=1[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-07 : 02:20:33
|
And if sql 2000 or earlierSELECT t.*FROM Table tINNER JOIN (SELECT ItemID,MAX(date) AS latest FROM Table GROUP BY ItemID) t1ON t1.ItemID=t.ItemIDAND t1.latest=t.Date |
|
|
mubash3r
Starting Member
2 Posts |
Posted - 2008-11-07 : 02:59:15
|
Thanks visakh16. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-07 : 03:00:38
|
Welcome |
|
|
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)tWHERE Seq=1 SELECT TOP 1000 [custid], [surname]FROM customer |
|
|
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 lINNER JOIN Customer cON c.CustId = l.Custid)tWHERE Seq=1
like above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|