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?
Don't know MSSQL but this should work for all database types:
SELECT DISTINCT T_AUTHOR,[OTHER_FIELDS]
FROM FORUM_TOPICS
WHERE [CLAUSES]
GROUP BY T_AUTHOR
ORDER BY [SORT_FIELD]
LIMIT [X]
Search is your friend “I was having a mildly paranoid day, mostly due to the fact that the mad priest lady from over the river had taken to nailing weasels to my front door again.”
I must say that I never really understood well the use of a GROUP BY clause outside of an aggregation query and I never use it. So I did look at this solution of yours with some curiosity, because I said to myself - this cannot work .
I wrote the query in MySQL this way:
SELECT DISTINCT T_AUTHOR, T_SUBJECT, T_LAST_POST FROM FORUM_TOPICS
GROUP BY T_AUTHOR
ORDER BY T_LAST_POST DESC
LIMIT 20
I then executed this over a live forum and compared it with the results of the same query, without the GROUP BY clause.
Both queries return 20 records (I used 20 to make sure I had multiple posts by the same author), but the query with the GROUP BY did not return the correct results, in terms of what is desired: last topics by different authors.
These are the results, no group by on the left, with group by on the right:
I removed the title to avoid identifiying the forum, but this shows what I said before. The last post date values are correct for the topics at the left, but not for the topics on the right. I can't explain what is happening, but this reinforces my willingness to only use GROUP BY in agregation queries alone.
I will try to post a solution that works in all cases.
After giving this some considerable thought, I don't think it can be solved by a "simple" (or even complex) SQL statement. The simplest approach I could think of would be a multi statement table valued function. I will write one.
in MSSQL you can't groupby T_AUTHOR if the select statement contains other columns, it will just error as the other columns are not in the aggregation function or the groupby.
in MSSQL you can't groupby T_AUTHOR if the select statement contains other columns, it will just error as the other columns are not in the aggregation function or the groupby.
I don't think that applies to a query that does not include aggregation functions, Huw.
this query should give a list of the last 20 distinct authors who posted and when, once those results are found you would need to squirt them ino a temporary table and then join that to the topi table to get the other info you need like topic_id etc, not a simple thing to do.
SELECT TOP (20) T_AUTHOR, MAX(T_LAST_POST) AS LastPost FROM FORUM_TOPICS GROUP BY T_AUTHOR ORDER BY LastPost DESC
in MSSQL you can't groupby T_AUTHOR if the select statement contains other columns, it will just error as the other columns are not in the aggregation function or the groupby.
I don't think that applies to a query that does not include aggregation functions, Huw.
it applies to any query with more than one column in the select statement, they must either be in the group by or in an aggregate function as in my example above.
it applies to any query with more than one column in the select statement, they must either be in the group by or in an aggregate function as in my example above.
You are right about this. Your example is not the best one to counter my earlier objection, since you do have an aggregation function in there, but I confirmed it anyway.
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
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.
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.