MySQL query question

[Jack]

ok... I have a little problem.

I want to run a select query. Example:

SELECT DISTICT topic_id
FROM posts
ORDER BY topic_time DESC

now, as expected, the order by doesn' t do anything. (except making the topic id go descending)

but... if i go

SELECT DISTINCT topic_id, topic_time
FROM posts
ORDER BY topic_time DESC

I get the correct sort order, but now i have more than one rows for each topic_id (becuase the topic_time for the topic_id s are different)

So... If you don't get what I said above, I'll try to now explain in just english.

I want to select unique topic_id. However, i want to order them by topic_time. The topic_time for each topic_id is different.

Can anyone help?

[Duke 13370]

You have to include the variable you will sort by to use it.

Otherwise strange things may occur

[Jeremy_Mc]

if you're just trying to get a specific one, why not use WHERE?

or is that not what you're doing (i've never seen 'DISTINCT')?


*jeremy

[AJ Quick]

Sounds like a phpBB type of script.. what are you trying to achieve?

PM me if you need some specific help.

[Jack]

ya, it's phpBB... but is really just because i'm not that great as SQL.

anyways...

a[100, 101, 102, 103, 104] b[3, 8, 3, 1, 8]

the a&b are the fields and 100 -> 3 ; 101 -> 8 ; ... ; 104 -> 8

(that's my best way of making a table)

now, for the output, I want: 1, 3, 8

yet, I want them in the order of 8, 1, 3

as you can see, I want the numbers sorted by the 'a' field DESC but I don't want repeats in the b field.

actually what i'm trying to do is (you better know the phpBB table layout) is select the topic_id from phpbb_posts sorted by post_time with unique (no repeats) of the topic_id.

can anyone help?

PS: DISTINCT, DISTINCT ROW whatevery you like.

[Jeremy_Mc]

are you trying to get the most recent posts?

if so, look on www.firstubergeeks.com/downloads.php we have a script for that on there...it's sloppy and uncommented, but if you want to download it i can fix that. just pm me before you do

*jeremy

[Jack]

Jeremy_Mc - that's almost what i want. However, the topics repeat. (Ex: if the past two posts were in the same topic, then that topic shows up twice) Is there anything you could do to not have that happen.

I've been playing with my sql statement for a while now, and can't figure it out.

[Jeremy_Mc]

Our script just does this when that happens:

The Post - by theirname
The Post [response name] - by theirname


I could edit to only show up once tho if needed.

*jeremy

[AJ Quick]

I don't know how good this info is, but you can order by 2 things at the same time. You can order by the topic_time, then order by the topic_id. Such is:

PHP Code:

ORDER BY topic_time, topic_id 


What it sounds like you really want is the group function of mySQL. With that you can group those of the same value together. So:

PHP Code:

GROUP BY topic_id 


Would give you the topic_ids all together, then you can sort by topic time.. so try this out for size:

PHP Code:

SELECT topic_id FROM posts GROUP BY topic_id ORDER BY topic_time DESC 


That should do everything. But you are going to want to look out for private forums, and other things you don't want it to catch. That should put you out where you need to be.. but I think you should also check out http://www.phpbbhacks.com/ for these types of things. (There is a most recent topics on any page type of code on there).

[Jack]

ok, to end the SQL saga here is what i got:

PHP Code:

SELECT t.topic_id, t.topic_title 
        FROM phpbb_topics AS t, phpbb_forums AS f, phpbb_posts AS p 
        WHERE p.post_id = t.topic_last_post_id AND t.forum_id = f.forum_id AND f.auth_read = 0 
        ORDER BY p.post_time DESC 
        LIMIT 0,15 


I would like to give many thanks to Brandon for reminding me that there is a field called topic_last_post_id (he really is the god of php/sql/web stuff ) oh, and thanks to everyone else who helped.

jack