Group only for "limit and offset" in MySQL
I ran into this issue while trying to paginate. My table is
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 data displayed like this, in groups of two:
-Fiat - Punto
Panda
-Ford - Mondeo
Mustang
So there are 2 brands, but 4 database results. Is it possible to have an on-demand limit and offset my results for two brands by showing all models for a brand?
Sorry if I don't understand!
source to share
To get a limit without using a keyword limit
, you can impose an invoice.
For example, given the table definition
create table cars (id int,
carBrand char(10),
carModel char(10));
it will give you all car models for the 2 best 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 carBrands
and then joins that car to get a list of all the cars in the top 2 brands. count(*) .... < 2
sets a limit. Consider 'Ford'
, for example, in your above data. In the case " Ford'
there are 3 brands that are < 'Ford'
in alphabetical order, so count(*)
above = 3. Since 3 is at least 2, no cars appear at the exit 'Ford'
.
The test data output will be as follows:
CARBRAND CARMODEL
Alfa Romeo Guilietta
Alfa Romeo Mito
Audi A3
Audi R8
Audi TT
Now you didn't say how you wanted to choose 2 brands - you just mentioned Ford and Fiat in your example - I don't know how you chose them. If you need anything other than the alphabetical criteria for ordering, this is doable but more difficult.
SQL Fiddle and results for all of this: http://sqlfiddle.com/#!2/33a8f/3
source to share
This is a matter of database design. Perhaps you should split your data into two tables model
(model names) and brand
(brands). 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 have not tested the code. No need in GROUP BY
my opinion.
source to share