The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
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?
نوشته شده در
So you want distinct entries. You'd use something like this:
Code:
strSql = "SELECT DISTINCT T_AUTHOR FROM " & strActivePrefix & "TOPICS"
نوشته شده در
Yup but how may I join it with other data with Topics table
SELECT TOPIC_ID, T_SUBJECT... ?
SELECT TOPIC_ID, T_SUBJECT... ?
نوشته شده در
Don't know MSSQL but this should work for all database types:
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.”
Code:
SELECT DISTINCT T_AUTHOR,[OTHER_FIELDS]
FROM FORUM_TOPICS
WHERE [CLAUSES]
GROUP BY T_AUTHOR
ORDER BY [SORT_FIELD]
LIMIT [X]fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”
نوشته شده در
Shaggy,
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:
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.
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:
Code:
SELECT DISTINCT T_AUTHOR, T_SUBJECT, T_LAST_POST FROM FORUM_TOPICS
GROUP BY T_AUTHOR
ORDER BY T_LAST_POST DESC
LIMIT 20
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.
