DISTINCT still showing duplicate records 1

[snowboardr]

Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

 

[r937]

with LIMIT 7, the number of rows is 7

perhaps what you want is the number of rows it would've returned if there were no LIMIT?

please see the FOUND_ROWS function in the manual

r937.com | rudy.ca

 

[snowboardr]

The problem with that is i have to know before i do the select... because i am doing a "posts participated in" count

 

[r937]

you have to know what before you do the select?

r937.com | rudy.ca

 

[snowboardr]

The number of records returned for that SQL statement... i forgot to remove the limit in the thing i copied... but it wasn't in the one i tested...

select distinct
       main.pid
     , main.puid
     , main.psubject
     , main.ptime
     , main.ptid
     , t1.tname tname1
     , main.ptid2
     , t2.tname tname2
  from forumpost as reply
inner
  join forumpost as main
    on main.pid = reply.preplyid
   and main.ptype = 0
   and main.puid <> reply.puid
inner
  join thread as t1
    on t1.tid = main.ptid
inner
  join thread as t2
    on t2.tid = main.ptid2
 where reply.puid = 2
   and reply.ptype = 1
order
    by main.ptime desc

 

[r937]

you want to know the number of rows returned by that query?

instead of, or in addition to the results of that query?

r937.com | rudy.ca

 

[snowboardr]

Yes i only want to know the number of rows that would be returned by that query i don't want to run the select itself just grab the number of rows that "would be" returned if it were ran...

thanks


Army : Combat Engineer : 21B

 

[r937]

well, unfortunately you're going to have to "sort of" run it in order to get that count

select count(*) as would_have_returned
  from (
     [i]put entire query here[/i]
       ) as thequery

r937.com | rudy.ca

 

[snowboardr]

so are you telling me thats going to be inafficient? Because we are talking about Replies by a user... which could be in the 1000s. A lot of times I keep track of the numbers as it occurs, ie if someone posts something then update their post count etc... but it will be a pain if i have to delete posts etc etc... then I have to update x users...

 

[r937]

well, no, replies by a specific user would identify that user in the WHERE clause

r937.com | rudy.ca