help with the quey - 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

General SQL Server Forums

New to SQL Server Programming

help with the quey

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2009-08-04 : 16:52:11

Hello All,

I have table called threaduser which has (threadID(int) , lastview(datetime), lastposted(datetime), lastreplied(datetime)) as columns. Each time when a reply or post is made this table is updated.

I need a query to update my dashboard with the latest updated time , along with that i need the threadid for which latest update was made.

If there was no threadid then its a simple query like select max(lastview), max(lastposted), max(lastreplied) from thread user.

But now i have different threadid to deal with( for example last reply would be on threadid 78 and lastview will be on 80).

help or suggestion on how to get all three in a single query.

Regards,
Mani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 17:02:15

Are you using SQL Server 2000, 2005 or 20008?

N 56°04'39.26"
E 12°55'05.63"

gxs8938
Starting Member

23 Posts

Posted - 2009-08-04 : 17:09:37

sql server 2005

Thanks,
mani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 17:46:03

[code]DECLARE @Sample TABLE
(
ThreadID INT,
LastView DATETIME,
LastPosted DATETIME,
LastReplied DATETIME
)

INSERT @Sample
SELECT 1, '20090101', '20090601', '20090701' UNION ALL
SELECT 2, '20090201', '20090501', '20090901' UNION ALL
SELECT 3, '20090301', '20090401', '20090801'

-- Peso 1
;WITH Yak(ThreadID, theDate, theCol, recID)
AS (
SELECT u.ThreadID,
u.theDate,
u.theCol,
ROW_NUMBER() OVER (PARTITION BY u.theCol ORDER BY u.theDate DESC) AS recID
FROM @Sample AS S
UNPIVOT (
theDate
FOR theCol IN (s.LastView, s.LastPosted, s.LastReplied)
) AS u
)

SELECT ThreadID,
theDate,
theCol
FROM Yak
WHERE recID = 1

-- Peso 2
SELECT ThreadID,
theDate,
theCol
FROM (
SELECT ThreadID,
theDate,
theCol
FROM (
SELECT TOP 1 ThreadID,
LastPosted AS theDate,
'LastPosted' AS theCol
FROM @Sample
ORDER BY LastPosted DESC
) AS d

UNION ALL

SELECT ThreadID,
theDate,
theCol
FROM (
SELECT TOP 1 ThreadID,
LastReplied AS theDate,
'LastReplied' AS theCol
FROM @Sample
ORDER BY LastReplied DESC
) AS d

UNION ALL

SELECT ThreadID,
theDate,
theCol
FROM (
SELECT TOP 1 ThreadID,
LastView AS theDate,
'LastView' AS theCol
FROM @Sample
ORDER BY LastView DESC
) AS d
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

gxs8938
Starting Member

23 Posts

Posted - 2009-08-04 : 22:16:11

Thanks a ton. works for me :)

--mani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources