| Author |
 Topic  |
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Carefree
Advanced Member
Philippines
4212 Posts |
 Posted - 02 August 2011 : 14:25:04
|
So you want distinct entries. You'd use something like this:
strSql = "SELECT DISTINCT T_AUTHOR FROM " & strActivePrefix & "TOPICS" |
 |
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 03 August 2011 : 04:30:25
|
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.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 07:09:32
|
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:
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 08:55:37
|
| 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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 10:01:56
|
quote:
Originally posted by HuwR
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 03 August 2011 : 10:26:18
|
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 |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 03 August 2011 : 10:28:14
|
quote:
Originally posted by ruirib
quote:
Originally posted by HuwR
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. |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 August 2011 : 10:42:51
|
quote:
Originally posted by HuwR
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.
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 03 August 2011 : 10:45:51
|
| 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 |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
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 |
MVC .net dev/test site | MVC .net running on Raspberry Pi |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
|
|
Topic |
|
SQL: Getting Forum Topics 1 per User?
that is what @topics is