Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: Help with Group By, Order By, LIMIT

    I want to retrieve data from a single table and then group it by column1, then subgroup it by column2 , then sort it within each one of those groups and finally display upto 10 results from each one of those sorted groups. Can someone please help with pseudocode as I'm having trouble making this work. Right now it looks like
    Select * from table_name where some_condition=TRUE group by column1, column2 order by column3. I'm not even sure how to work the limit clause into this statement as this causes only 10 values to be retrieved overall not 10 from every group.

  2. #2
    Join Date
    Feb 2008
    Location
    Bandung - Indonesia
    Posts
    15
    Select * from table_name
    where some_condition=TRUE
    group by column1, column2
    order by column1, column2 ASC LIMIT 10

    ASC - Ascending
    DESC - Descending , you choose.
    Forum Informatika - Indonesian Informatics Online Community - http://if.web.id

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    when you say group the data do you mean use a group by clause to compress like data (i.e. count how many cars you have irrespective of make/model?)

    it sounds to me that you are misusing group in a database context and by group you mean put all cars in order and then trucks and within cars all yellow cars and then red.

    If I'm misunderstanding perhaps some sample data and your expected output would clarify.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT column1
         , column2
         , column3
      FROM daTable AS T
     WHERE some_condition=TRUE
       AND ( SELECT COUNT(*) 
               FROM daTable  
              WHERE some_condition=TRUE
                AND column1 = T.column1
                AND column2 = T.column2
                AND column3 > T.column3 ) < 10
    GROUP
        BY column1
         , column2
    ORDER
        BY column1
         , column2
         , column3 DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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