Help with Group By, Order By, LIMIT

pgb205 · #1 (**permalink**) 11-25-08, 14:54

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.

galih · #2 (**permalink**) 01-04-09, 10:01

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.

guelphdad · #3 (**permalink**) 01-04-09, 16:19

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.

r937 · #4 (**permalink**) 01-04-09, 16:41

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

Internet Services

Web Development

Digital Marketing

Consumer Tech