SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex query involving grouping and ordering

    Sorry if I go too indepth here but I'm trying to explain this the best I can. If you have any questions please post them. In the following I use groups to refer to how the data is grouped.

    For the question, two columns will work, lets say topic_id and date. The actual table actually has more.

    What I'm trying to achive is to have all the topic_ids listed togeather, so a table with the following rows for topic_id look like:

    1,2,3,4,2,3

    Will be returned like:

    1,2,2,3,3,4

    I was able to obtain results like that by using a simple order by clause in the select statement, but then I needed something else.

    I want the above groups of 1,2,3,4 to be returned in an order based on the newest date in each group.

    To clarify the above, heres an example, with the biggest date occuring most recently:

    Row 1 - Group "1"
    #####
    topic_id:1
    date: 1

    Row 2 - Group "2"
    #####
    topic_id: 2
    date: 2

    Row 3 - Group "2"
    #####
    topic_id:2
    date: 4

    Row 4 - Group "3"
    #####
    topic_id:3
    date: 3

    Just so were clear, the dates go in this order: Row #1, Row #2, Row #4, Row #3. The "Group" comment I added to each row is based on its topic_id.

    In my testing I could only get the date to be returned in order regardless of my topic_id posts: Row #1, Row #2, Row #4.

    However, I need the topic_id to be grouped first: Row #1, Row #4, Row #2, Row #3.

    Group #2 comes last because it has the newest date in its group, Group #3 behind it, with Group #1 coming first.

    Group #1 Newest Date - 1
    Group #2 Newest Date - 4
    Group #3 Newest Date - 3

    So the groups need to come in the order of: #1, #3, #2. Group #2 has multiple rows, so those should be returned based on the date. Row #3 is higher then row Row #2, so it comes last.

    A friend just commented on how it may be like a forum with the topics and posts in one table. When a post is made the topic is brought to the top of the list.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    Code:
    select *
    from tablename
    order by topic_id,`date`
    Chagh

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, but his does not work, it orders the date column inside of the topic_id groups. I need the topic_id groups to be ordered based on the newest date column in each group.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you mean:
    Code:
    select *, max(`date`)
    from tablename
    group by topic_id
    order by max(`date`) desc
    this code gives one instance of each topic_id, i don't know how you can get all of them in one simple query but you can join this select with the tablename on topic_id to get all topics. or you can use subselects.

    Chagh

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select q.*, 
       (select max(dateColumn) 
         from t
        where t.topic_id = q.topic_id) as dc
    from t as q
    order by dc desc

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you! Just by looking at it I thought it wasn't going to work correctly (hell, I couldn't even tell what it did) but it seems to work just the way I need it to. Looking over it again it kinda makes sense, add another column to each row representing the max date in the group.

    Thank you again!

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sigh, is there a solution without using subquerys?

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone have any ideas?

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select
        t1.topic_id
      , max(t2.`date`) as dc
    from thetable t1
    join thetable t2 on
      t2.topic_id = t1.topic_id
    group by t1.topic_id
    having t1.`date` = dc
    i'm not in front of a computer right now, so no gaurantees on the results.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Pests
    Sigh, is there a solution without using subquerys?
    yes, possibly there might be
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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