Re: Workings of a forum

groovee_at_cyberdude.com writes:
>What I want is, on the main page of the site, to display the thread titles =
>WITH THE THREAD WITH THE LATEST POST FIRST (just as is done in so many plac=

  Main.sql

\W SET sql_mode = 'ANSI,TRADITIONAL';
SET SQL_MODE = 'PIPES_AS_CONCAT';
DROP SCHEMA S; CREATE SCHEMA S; USE S; CREATE TABLE POST( POST INT, THREAD INT, TIME INT );

INSERT INTO POST( POST, THREAD, TIME )VALUES( 10, 100, 20 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 11, 100, 10 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 12, 100, 30 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 13, 101, 60 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 14, 101, 50 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 15, 101, 40 );
INSERT INTO POST( POST, THREAD, TIME )VALUES( 16, 101, 60 );

CREATE VIEW MAXTIME AS
SELECT POST.THREAD, MAX( POST.TIME ) AS TIME FROM POST GROUP BY THREAD; CREATE VIEW THREADS_ORDERED_BY_LATEST_POST_IN_THREAD AS SELECT POST.THREAD, POST.POST AS LATEST_POST, MAXTIME.TIME AS TIME_OF_LATEST_POST
FROM POST JOIN MAXTIME
ON POST.THREAD = MAXTIME.THREAD AND
POST.TIME = MAXTIME.TIME GROUP BY POST.THREAD ORDER BY TIME_OF_LATEST_POST DESC; SELECT * FROM THREADS_ORDERED_BY_LATEST_POST_IN_THREAD;   transcript

+--------+-------------+---------------------+

+--------+-------------+---------------------+
|    101 |          13 |                  60 |
|    100 |          12 |                  30 |
+--------+-------------+---------------------+