#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    1
    Rep Power
    0
    OK, I have a Table, three of the fields are PostID, ThreadID, and DatePosted. Here is the kind of data I will have in the table:

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    PostID | ThreadID | DatePosted
    -------+----------+---------------------
    1 | 1 | 2000-11-10 08:40:00
    2 | 1 | 2000-11-10 11:23:00
    4 | 1 | 2000-11-13 04:00:24
    6 | 2 | 2000-10-23 06:45:26
    7 | 2 | 2000-12-05 04:34:54
    [/code]

    I want to be able to get the first and last posted rows but not the ones in between for each ThreadID. From the data above, I'd like to get this (sorted by LastPostDate):

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    ThreadID | FirstPostID | LastPostID | FirstPostDate | LastPostDate
    ---------+-------------+------------+---------------------+---------------------
    2 | 6 | 7 | 2000-10-23 06:45:26 | 2000-12-05 04:34:54
    1 | 1 | 4 | 2000-11-10 08:40:00 | 2000-11-13 04:00:24
    [/code]

    What SELECT what I have to send to get it?
  2. #2
  3. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,646
    Rep Power
    4492
    Here you go...

    SELECT ThreadID, MIN(PostID) as FirstPostID, MAX(PostID) as LastPostID, MIN(DatePosted) as FirstPostDate, MAX(DatePosted) as LastPostDate FROM your_table GROUP BY ThreadID ORDER BY LastPostDate;

    Have fun....

    ---John Holmes...

    ------------------
    *************************************************************
    * The manual can probably answer 90% of your questions...
    *
    * PHP Manual. www.php.net/manual
    * MySQL Manual: www.mysql.com/documentation/mysql/bychapter
    *************************************************************

Similar Threads

  1. Replies: 3
    Last Post: February 11th, 2004, 09:30 AM
  2. mysql data lost
    By imola in forum MySQL Help
    Replies: 1
    Last Post: February 10th, 2004, 08:39 AM
  3. Replies: 1
    Last Post: February 5th, 2004, 07:17 AM
  4. Replies: 0
    Last Post: January 30th, 2004, 09:46 PM
  5. Writing to a word file using data from Mysql
    By Liz213 in forum Java Help
    Replies: 5
    Last Post: January 23rd, 2004, 12:38 AM

IMN logo majestic logo threadwatch logo seochat tools logo