DISTINCT is just not working...need a pointer

[snowboardr]

I am trying to get my records yet only displaying 1 of each tid ID number, and this first one errors.

And the Second sql does not do anything.

---------------------------------------------------

'rsReply.Source = "SELECT rid,ruid,rtid,rfid,rposted,DISTINCT tid,tsubject,fid,ftitle FROM ureplies,utopics,uforum WHERE ruid=" & sUserID & " AND fid=rfid AND tid=rtid ORDER BY rid DESC LIMIT 10"

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-3.23.51-nt]You have an error in your SQL syntax near 'DISTINCT tid,tsubject,fid,ftitle FROM ureplies,utopics,uforum WHERE ruid=1 AND f' at line 1

/forum/profile/view.asp, line 240

---------------------------------------------------

rsReply.Source = "SELECT DISTINCT tid,tsubject,rid,ruid,rtid,rfid,rposted,fid,ftitle FROM utopics,ureplies,uforum WHERE ruid=" & sUserID & " AND fid=rfid AND tid=rtid ORDER BY rid DESC LIMIT 10"


Any ideas?

- Jason

 

[sleipnir214]

The keyword "distinct" must appear before the column list in a select statement in MySQL.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[snowboardr]

I do in the second Query, but that doesn't do anything, it still is showing duplicates.

rsReply.Source = "SELECT DISTINCT tid,tsubject,rid,ruid,rtid,rfid,rposted,fid,ftitle FROM utopics,ureplies,uforum WHERE ruid=" & sUserID & " AND fid=rfid AND tid=rtid ORDER BY rid DESC LIMIT 10"

- Jason

 

[swampBoogie]

It is your notion of distinct that is at fault. Distinct is always applied to a complete record.

E.g.

A B
----
1 2
1 3

You say that you want distinct values for A but what value for B should be returned?

 

[snowboardr]

Well if A1 is the same as A2 then I dont want to show A2.

Do you know how I could do that? But I understand what you are saying.

- Jason

 

[r937]

well, if you're talking about A and B only, and you don't care which B, then you could just say

select A, min(B) from yourtable group by A

however, in your actual query, you have columns from 3 tables, and i'm guessing your table relationships are

forum --1-to-many-- topics --1-to-many-- replies

so if you're showing only one row per topic, which reply do you want?

it's usually the latest, but no matter which it is, you will probably require two queries if you cannot use a subquery (implemented in version 4.1)

see
The Rows Holding the Group-wise Maximum of a Certain Field

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

rudy

http://r937.com/

 

[r937]

on the other hand, if you have

reply1 topic7
reply2 topic7
reply3 topic7
reply1 topic8
reply2 topic8
reply1 topic9

and what you want is to show only

reply1 topic7
reply2
reply3
reply1 topic8
reply2
reply1 topic9

then you do not do that in sql, you do that in your calling script or programming language

rudy

 

[snowboardr]

I guess maybe im not explaining it correctly.

I am displaying the following things:

reply subject(tid) | forum name(fid) | reply date(rposted)

I only want to display one of each topic id in my query.


- Jason

www.vzio.com
ASP WEB DEVELOPMENT

 

[r937]

sorry, i don't get it

you mean like this? --

reply1 topic7
reply2
reply3
reply1 topic8
reply2
reply1 topic9


how about some examples of the way you want to see it

 

[snowboardr]

Using your example:
reply1 topic4
reply2 topic4
reply3 topic3
reply4 topic5
reply5 topic3

would output:

reply1 topic4
reply3 topic3
reply4 topic5

reply id = rid
topic id = tid

 

[r937]

okay, now we're getting some place!

how do you wish to pick the rid that goes with your single tid?

by the way, did you read that mysql.com article?

 

[snowboardr]

Okay the topics are the start of the thread. (forum) and the replies I put the topic id in the reply table in the topic column. I only want to show 1 reply from each topic. (tid)

3 tables: utopics, ureplies, uforum

uforum stores the forum name

 

[r937]

i know this is difficult, but i'm willing to keep trying until you get it

you say "I only want to show 1 reply from each topic"

and i say again, which one?

by the way, did you read that mysql.com article?

 

[snowboardr]

I read the article but this pertains to version 4.1 mysql and my host only has 3.23

You keep asking me which one, but I want every reply accept I don't want duplicates replies in a topic I don't understand why you ask which one?

I have tried this:

If sLastTopicID <> sTopicID then
sLastTopicID = sTopicID
Else
rs.movenext
End If

to make it try and goto the next record if there is a duplicate topic id, but this only hides 2 or 3 duplicates out of 5 duplicate id's in a row...

- Jason

 

[r937]

you want every reply in a topic but you don't want duplicate replies in a topic?

that doesn't make sense

why do i keep asking? because you said you wanted this --

reply1 topic4
reply2 topic4
reply3 topic3
reply4 topic5
reply5 topic3

reduced to this --

reply1 topic4
reply3 topic3
reply4 topic5

so yes, you are selecting &quot;which one&quot; out of the multiple replies for a given topic to keep

so, i say again, how do you define which one of the replies to keep?

certainly it can not involve the ids, because they're unique, right?

by the way, that article was very specifically not for 4.1

in 4.1 you can use subqueries so there is no problem

the article discusses what you do in versions before 4.1

if you want only the earliest reply in a topic, then you want the row out the group with the MIN(postdate)

the article talks about the row with the MAX() value, in your case you want the MIN value

rudy

 

[snowboardr]

Ok. I want to select 1 reply out of each topic. So now do you understand which reply I want to keep?

I want to keep only 1 reply from each topic posted. So this should answer the question how you define which reply to keep. I hope.

- Jason

 

[r937]

okay, i think the frustration level on your part must be almost as high as mine, so i'm going to sign off for tonight

you keep saying you only want 1 reply out of several in a topic, but you don't give a clue as to which one, you just say you want only one

i suggested maybe the earliest, but it sounds like you want a random one

but in any case, it doesn't really matter which one, because you can't do what you want in one query anyway!!

here's my final advice:

read the article and use a temp table

good luck


rudy