| T O P I C R E V I E W |
| SiSL |
Posted - 02 August 2011 : 12:44:06
Is there an easy way of like getting latest x number of topics but only 1 topic per author of topic or instead of author not having same title of topics (like spammed ones)? Like not gettings "SiSL"'s 3 topics if he opened 3 of last 10 topics but 1 topic from SiSL and rest 1 topic per author... with Topic ID's and such for SQL Server?
|
| 15 L A T E S T R E P L I E S (Newest First) |
| HuwR |
Posted - 03 August 2011 : 13:10:22
 yes, I ought to finish what I was doing beore getting side tracked. |
| ruirib |
Posted - 03 August 2011 : 12:26:56
From an execution point of view, I tested both ways with a database from a live Snitz forum and your option had a higher subtree cost (0,8 vs. 3.17) and a higher number of scans and logical reads (rather big difference here). Probably a disadvantage on a high traffic forum.
I had performed a similar comparison before, with Snitz too (query performance has always been of interest to me) and using derived tables wasn't actually faster than using temporary tables, probably because the number of records involved was much higher. I thought it was interesting to compare it in this situation.
This is fun, but I have already spent 2 or 3 hours with this. I better go do something more boring .
|
| HuwR |
Posted - 03 August 2011 : 11:59:45
I still think my first attempt was the simplest I just should have dropped the #TempTopics table after doing the select |
| ruirib |
Posted - 03 August 2011 : 11:55:52
quote:
Originally posted by HuwR
well, to be honest four nested selects isn't exactly simple
It's not simple ?!!   
I promise I wrote them one at a time  . |
| HuwR |
Posted - 03 August 2011 : 11:50:51
well, to be honest four nested selects isn't exactly simple |
| ruirib |
Posted - 03 August 2011 : 11:34:58
This was fun, now to get back to work . Of course, I am hoping no one noticed that I contradicted myself and ended up solving it all with a very, very, simple () SQL statement. |
| ruirib |
Posted - 03 August 2011 : 11:25:52
quote:
Originally posted by HuwR
code posted above to do the same with just an inline query, no functions needed
Oh yeah?
Look, no temp tables:
SELECT T.* FROM FORUM_TOPICS T INNER JOIN
(SELECT T_AUTHOR, (SELECT TOPIC_ID FROM FORUM_TOPICS T2 WHERE T2.T_AUTHOR=T1.T_AUTHOR AND T2.T_LAST_POST=T1.LASTPOST) As T_ID
FROM (
SELECT TOP (10) T_AUTHOR, MAX(T_LAST_POST) AS LastPost
FROM FORUM_TOPICS
GROUP BY T_AUTHOR
ORDER BY LastPost DESC
)T1
) As MyT ON MyT.T_ID=T.TOPIC_ID
ORDER BY T_LAST_POST DESC
This one also runs on MySQL, just by replacing TOP by LIMIT (and placing limit at the end of the subquery). |
| ruirib |
Posted - 03 August 2011 : 11:21:16
quote:
Originally posted by HuwR
code posted above to do the same with just an inline query, no functions needed
Go away, mine is much neater . |
| HuwR |
Posted - 03 August 2011 : 11:17:14
code posted above to do the same with just an inline query, no functions needed |
| ruirib |
Posted - 03 August 2011 : 11:11:00
quote:
Originally posted by HuwR
well, you are infact creating a temp table that is what @topics is
Yeah, just in memory... |
| HuwR |
Posted - 03 August 2011 : 11:09:20
you could rewrite my query as follows to use an in emory table
SELECT DISTINCT TOP (20) T_AUTHOR, MAX(T_LAST_POST) OVER(PARTITION BY T_AUTHOR) AS LastPost
INTO @TempTopics
FROM FORUM_TOPICS
ORDER BY LastPost DESC;
SELECT * FROM FORUM_TOPICS Tp INNER JOIN @TempTopics ON
Tp.T_AUTHOR = @TempTopics.T_AUTHOR AND Tp.T_LAST_POST = @TempTopics.LastPost
actually to use an in memory table i is a bit more long winded.
DECLARE @TempTopics TABLE (
T_AUTHOR INT,
LastPost VARCHAR(14) );
INSERT @TempTopics(T_AUTHOR,LASTPOST)
SELECT DISTINCT TOP (20) T_AUTHOR, MAX(T_LAST_POST) OVER(PARTITION BY T_AUTHOR) AS LastPost
FROM FORUM_TOPICS
ORDER BY LastPost DESC;
SELECT * FROM FORUM_TOPICS Tp INNER JOIN @TempTopics Tt ON
Tp.T_AUTHOR = Tt.T_AUTHOR AND Tp.T_LAST_POST = Tt.LastPost |
| HuwR |
Posted - 03 August 2011 : 11:07:44
well, you are infact creating a temp table that is what @topics is
|
| ruirib |
Posted - 03 August 2011 : 11:01:04
No temp tables here:
CREATE FUNCTION GetLastTopicsOneByPoster()
RETURNS @topics TABLE
(
MEMBER_ID int PRIMARY KEY,
TOPIC_ID int
)
As
BEGIN
INSERT INTO @topics
SELECT T_AUTHOR, (SELECT TOPIC_ID FROM FORUM_TOPICS T2 WHERE T2.T_AUTHOR=T1.T_AUTHOR AND T2.T_LAST_POST=T1.LASTPOST) As T_ID
FROM (
SELECT TOP (10) T_AUTHOR, MAX(T_LAST_POST) AS LastPost
FROM FORUM_TOPICS
GROUP BY T_AUTHOR
ORDER BY LastPost DESC
) As T1
RETURN
END
and then
SELECT T.* FROM FORUM_TOPICS T INNER JOIN GetLastTopicsOneByPoster() G On T.TOPIC_ID=G.TOPIC_ID
Must say that I "pirated" from your initial query, Huw. I was following another path but this one is better. It returns just 10, but it's pretty easy to change it to return any number, passed as a parameter. |
| HuwR |
Posted - 03 August 2011 : 10:56:28
ok, here goes, this should return the enire topic record for the last twenty topics posted to(onlythe latest topic for an author)
SELECT DISTINCT TOP (20) T_AUTHOR, MAX(T_LAST_POST) OVER(PARTITION BY T_AUTHOR) AS LastPost
INTO #TempTopics
FROM FORUM_TOPICS
ORDER BY LastPost DESC;
SELECT * FROM FORUM_TOPICS Topics, #TempTopics
WHERE Topics.T_AUTHOR = #TempTopics.T_AUTHOR AND Topics.T_LAST_POST = #TempTopics.LastPost |
| ruirib |
Posted - 03 August 2011 : 10:50:04
quote:
Originally posted by HuwR
you can do it without the group by, but it still requires an agregate function, but it is just not possible to get all he info required in a simple or complex query because you can't return all the info you need like topicid etc inthe same query
Yes, I had reached the same conclusion.
I was trying to use a function returning a table, but not a simple thing to do either, not without using cursors, which I am trying to avoid. |
