January 24, 2013 at 6:19 pm
How do you get the most recent record ie it would be 150 for acct 1 and 75 for acct 2.
CREATE TABLE [dbo].[Table_1](
[Acct] [int] NULL,
[Amt] [int] NULL
) ON [PRIMARY]
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,100)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,200)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,150)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,50)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,300)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,125)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,75)
January 24, 2013 at 6:45 pm
sstacy7 (1/24/2013)
How do you get the most recent record ie it would be 150 for acct 1 and 75 for acct 2.CREATE TABLE [dbo].[Table_1](
[Acct] [int] NULL,
[Amt] [int] NULL
) ON [PRIMARY]
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,100)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,200)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(1,150)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,50)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,300)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,125)
INSERT INTO [Test].[dbo].[Table_1] ([Acct],[Amt])
VALUES(2,75)
You can't. Add a timestamp column to the table then, use it to figure out the lastest row.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 25, 2013 at 2:05 am
Add a datetime column that defaults to getdate(), that's the date inserted. You can then max that and group by account to get the latest rows
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2013 at 7:12 am
select m2.Acct1,m2.Amt
from
(select n1.Acct,n1.max_insert_no
from
(select Acct,MAX(last_insert)max_insert_no
from
(select Acct,ROW_NUMBER() over (partition by Acct order by Acct)last_insert from Table_1)n
group by Acct)n1
)n2
join
(select Acct Acct1,Amt,ROW_NUMBER() over (partition by Acct order by Acct)last_insert1 from Table_1)m2
on n2.Acct = m2.Acct1 and n2.max_insert_no = m2.last_insert1
First get the last insert id, and then get the max insert id from the obtained result set in the sub query , and then join the table m2 , which has the list, with row numbers with respect to Acct
January 25, 2013 at 7:34 am
Gowthu (1/25/2013)
select m2.Acct1,m2.Amtfrom
(select n1.Acct,n1.max_insert_no
from
(select Acct,MAX(last_insert)max_insert_no
from
(select Acct,ROW_NUMBER() over (partition by Acct order by Acct)last_insert from Table_1)n
group by Acct)n1
)n2
join
(select Acct Acct1,Amt,ROW_NUMBER() over (partition by Acct order by Acct)last_insert1 from Table_1)m2
on n2.Acct = m2.Acct1 and n2.max_insert_no = m2.last_insert1
First get the last insert id, and then get the max insert id from the obtained result set in the sub query , and then join the table m2 , which has the list, with row numbers with respect to Acct
That's not necessarily going to get the last inserted row. It'll get any of the rows, whichever one SQL runs across last as it reads, That depends on indexes and access strategy. Trivial to prove too. See attachment
There is no implicit order in a table, you're said in that, put the row numbers in any order that SQL likes, just partitioned by the account. It might work. It might not. It might work initially and not work later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2013 at 9:21 am
Ya, thanks for the info. I did not check by creating the indexes.
I just took the code snippet from sstacy7, and just tried my query on that.
January 25, 2013 at 10:01 am
Not just indexes. A change in the data volume in the table could easily have the same effect. The query is non-deterministic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply