www.webdeveloper.com
Results 1 to 3 of 3

Thread: ORDER BY from 2 tables with different row names

  1. #1
    Join Date
    May 2007
    Posts
    52

    ORDER BY from 2 tables with different row names

    here is a rundown,
    i am creating a basic forum but cant get the posts to show in order depending on weater the topic or reply is newer if you get what i mean

    table1 - table2

    contain following rows (datetime)

    dt - a_dt

    i need to
    select * from table1 but ORDER BY dt - a_dt


    any help would be appreciated
    Last edited by SharingDOODLES; 07-09-2010 at 06:54 PM.

  2. #2
    Join Date
    Jul 2010
    Location
    Sofia, Bulgaria
    Posts
    49
    I'm not sure if I understood, but let's assume:
    `table1` (topics) with columns `id` (int) and `dt` (datetime),
    `table2` (replies) with columns `topicid` (int) and `a_dt` (datetime).
    If you want to get the topics with their last activity (whether the topic creation or its last reply), you'll need something like this:
    Code:
    select t1.id, ifnull(max(t2.a_dt),t1.dt) as lastactivity
    from table1 as t1
    left join table2 as t2 on t1.id = t2.topicid
    group by t1.id
    order by lastactivity;
    I hope this helps.

  3. #3
    Join Date
    May 2007
    Posts
    52
    i have ran the sql in my database and it seems to do what i needed, i say needed as i have already sorted it but the looooong way around,

    added a new colum (lastupdate) and used the value from that, if the same as the topic time, display 'no replys' instead of the time,

    like i said, the loooon way round lol,

    but thank you as it is still much appreciated, and i will have to take a look at the "joins" and "group by" as i just dont get it all lol

    Have Fun

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles