get the latest value in a column

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 2008 Forums

Transact-SQL (2008)

get the latest value in a column

Author

Topic

hari4u6
Starting Member

12 Posts

Posted - 2009-12-21 : 01:02:20

Hello all,
i want to get the latest value for a given id,
coz i want to use tat value to insert in a new record.

Hari.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-21 : 01:28:11

Post your table and expected output

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hari4u6
Starting Member

12 Posts

Posted - 2009-12-21 : 02:34:42

ForumReplyId ForumThreadId Subject Description
------------------------------------------------
1 1 FAQ dasdadad
2 1 Re:FAQ adsadasad

Expected output
for threadId '1' subject Re:FAQ

ForumReplyId is Autogenerated.

Hari.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-21 : 03:29:50

Try this..

select * from table_name where ForumReplyId =(
select max(ForumReplyId) from table_name where ForumThreadId =1)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-21 : 03:46:00


SELECT FORUMREPLYID,FORUMTHREADID,SUBJECT,DESCRIPTION FROM
(
SELECT * , ROW_NUMBER() OVER(PARTITION BY FORUMTHREADID ORDER BY FORUMREPLYID DESC) AS SEQ
FROM #TEMP
) T
WHERE T.SEQ = 1

-------------------------
R...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-21 : 03:56:45

quote:
Originally posted by senthil_nagore

Try this..

select * from table_name where ForumReplyId =(
select max(ForumReplyId) from table_name where ForumThreadId =1)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

can you test your query with below data set..


Create table #temp (ForumReplyId INT,ForumThreadId INT, Subject varchar(50),Description varchar(50))


Insert into #temp

select 1 ,1 ,'FAQ'	,'first'         union all
select 2 ,1 ,'Re:FAQ'	,'firstmaximum'  union all
select 1 ,2 ,'FAQ'	,'second'        union all
select 2 ,2 ,'Re:FAQ'	,'secondmaximum'

-------------------------
R...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-21 : 04:03:39

quote:
Originally posted by rajdaksha

quote:
Originally posted by senthil_nagore

Try this..

select * from table_name where ForumReplyId =(
select max(ForumReplyId) from table_name where ForumThreadId =1)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

can you test your query with below data set..

Create table #temp (ForumReplyId INT,ForumThreadId INT, Subject varchar(50),Description varchar(50))

Insert into #temp

select 1 ,1 ,'FAQ' ,'first' union all
select 2 ,1 ,'Re:FAQ' ,'firstmaximum' union all
select 1 ,2 ,'FAQ' ,'second' union all
select 2 ,2 ,'Re:FAQ' ,'secondmaximum'

-------------------------
R...

I miss the condition, thanks to point out

select * from table_name where ForumReplyId =(
select max(ForumReplyId) from table_name where ForumThreadId =1)
and ForumThreadId =1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-21 : 04:31:37

Hi
Try this for all data set's


SELECT FORUMREPLYID,FORUMTHREADID,SUBJECT,DESCRIPTION FROM
(
SELECT * , ROW_NUMBER() OVER(PARTITION BY FORUMTHREADID ORDER BY FORUMREPLYID DESC) AS SEQ
FROM #TEMP
) T
WHERE T.SEQ = 1

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources