DISTINCT, ORDER BY, and LIMIT conflict?

RandyTayler

I have a bulletin board I built from scratch. (Probably the first mistake.) I'm trying to get the main page to list the 20 threads that were most recently posted on.

In the past, I've simply used the following:

[FONT=courier new]$threadinfo=mysql_query ("SELECT ThreadID, TimePosted FROM Board ORDER BY TimePosted DESC", $db);[/FONT]

and then

[FONT=courier new]while (($thread=mysql_fetch_array($threadinfo)&&($i<20)){[/FONT]
[FONT=courier new]if (!$posts["$thread[ThreadID]"]) { [/FONT]
//do stuff, shove info in $poststring

//increment $i
[FONT=courier new]$posts["$thread[ThreadID]"]=$poststring;
}
}[/FONT]

That worked fine.

Now, however, I've tried to add LIMIT $rowstart, $rowcount, and things get screwy.

When I use [FONT=courier new]mysql_query ("SELECT DISTINCT ThreadID FROM Board ORDER BY TimePosted DESC LIMIT $rowstart, $rowcount", $db);[/FONT] I get the threads IN ORDER OF THEIR THREAD NUMBER DESCENDING.

Odd, I thought. So I put back in a selection of TimePosted, giving me [FONT=courier new]mysql_query ("SELECT DISTINCT ThreadID, TimePosted FROM Board ORDER BY TimePosted DESC LIMIT $rowstart, $rowcount", $db);[/FONT] which returns the threads in the correct order, but they're no longer DISTINCT. I get
[FONT=courier new]Thread: 1383 TimePosted: 2003-06-19 03:30:32
Thread: 1384 TimePosted: 2003-06-19 03:17:16
Thread: 1377 TimePosted: 2003-06-19 03:16:03
Thread: 1377 TimePosted: 2003-06-19 02:48:18
Thread: 1377 TimePosted: 2003-06-19 02:34:56
Thread: 1383 TimePosted: 2003-06-19 02:13:04
Thread: 1383 TimePosted: 2003-06-19 01:03:23
Thread: 1380 TimePosted: 2003-06-19 01:02:57[/FONT]

Can anyone see what I'm doing wrong? Is there some compatibility issue with DISTINCT and LIMIT or ORDER BY?

barand

Why do you say they're not distinct. I can't see any rows that are the same? All the times are different.

RandyTayler

Right. But I just want distinct ThreadIDs.

BeastRider

Yah... DISTINCT and ORDER BY seems wierd, but I think this will work. It is predicated upon the fact the the timestamps within each threadid are unique and increasing.

Ooops. Doesn't work!

$query = "SELECT ThreadID,
          MAX(TimePosted) AS TimePosted
          FROM Board
          GROUP BY TimePosted DESC
          LIMIT 0,10";

RandyTayler

Thanks.

I've never used GROUP BY before, but shouldn't it be GROUP BY ThreadID, not TimePosted?

EDIT: I just tested it, and GROUP BY TimePosted comes back invalid, and GROUP BY ThreadID comes back with my ThreadIDs in ASCENDING order.

😕

BeastRider

Originally posted by RandyTayler
Thanks.

I've never used GROUP BY before, but shouldn't it be GROUP BY ThreadID, not TimePosted?

Yes, it should. When you group by thread id the statement works, but of course, you want to be ordered by time posted.

RandyTayler

THERE we go:

SELECT ThreadID,
MAX(TimePosted) AS TimePosted
FROM Board
GROUP BY ThreadID
ORDER BY TimePosted DESC
LIMIT $result_count, $limit

Thanks Beast!