Group by only for "limit and offset" in MySQL

Question

Whilst trying to do pagination I've run into this problem. My table-

ID   CarBrand    Car Model
---------------------------
1    Alfa Romeo  Guilietta
2    Alfa Romeo  Mito

3    Audi        A3
4    Audi        R8
5    Audi        TT

6    Fiat        Punto
7    Fiat        Panda

8    Ford        Mondeo
9    Ford        Mustang

10   Nissan      Almera
11   Nissan      Note
12   Nissan      Qashqai

13   Toyota      Aygo
14   Toyota      Prius

15   Volkswagen  Beetle
16   Volkswagen  Golf
17   Volkswagen  Polo
18   Volkswagen  Up

I have the data displayed like so, in groups of two:

-Fiat  - Punto
         Panda

-Ford  - Mondeo
         Mustang

So there are 2 brands but 4 database results. Is it possible to have a query limit and offset my results to two brands while showing all the models for the brand?

Sorry if I'm not clear!

Answer 1

It is clear. Try this:

select * from t t1
join (
  select distinct carBrand from t
  limit 2
) s on t1.carBrand = s.carBrand

Before the limit 2 apply the ordering you want.

Answer 2

To get a limit, without using the limit keyword, you can impose a count.

For example, given the table definition

create table cars (id int,
                   carBrand char(10),
                   carModel char(10));

this will give you all the Car Models for the top 2 Car Brands

select cars.carBrand, cars.carModel
from cars
where  ((select count(*) from 
           (select distinct carBrand from cars) as carBrands
        where carBrands.carBrand < cars.carBrand) < 2)
order by cars.carBrand, cars.carModel;

This creates an inline table just listing the carBrands and then joins this back to cars to get the list of all cars that are in the top 2 brands. The count(*) .... < 2 enforces the limit. Consider 'Ford', for example, in your above data. In 'Ford''s case, there are 3 brands that are < 'Ford' alphabetically, so the count(*) above = 3. Since 3 is not less than 2, no 'Ford' cars appear in the output.

The output on your test data would be:

CARBRAND    CARMODEL
Alfa Romeo  Guilietta
Alfa Romeo  Mito
Audi        A3
Audi        R8
Audi        TT

Now, you didn't say how you wanted to pick the 2 brands -- you just listed Ford and Fiat in your example -- I don't know how you happened to pick those. If you want something other than alphabetical criteria for ordering, that's doable, but harder.

SQL Fiddle and results for all this: http://sqlfiddle.com/#!2/33a8f/3

Answer 3

It's a matter of database design. Maybe you should split your data into two tables model (model names) and brand (brand names). Then you can write a query like this:

SELECT m.name, b.name
FROM model m
INNER JOIN brand b
WHERE b.id IN (
    SELECT id
    FROM brand
    ORDER BY name ASC
    LIMIT 2 OFFSET 0
)

I did not test the code. No need for GROUP BY in my opinion.