CODEWITHSUNDEEP
mysqlsql
Richie
Richie

Reputation: 430

Trying to limit the amount be shown in a mysql result

I have a table that stores id, item, food type, image and price

I want to limit the result to one of each type sold in the store e.g. dog food, cat food, dvd in descending order Example of what is held in mysql database table: Nunca

id      item                    type        image   price
=========================================================
1       Pedigree Pouches        dog food    img1    $4.99
2       Cesar Classic Selection dog food    img2    $3.99
3       Meaty Strips Chicken    dog food    img3    $2.99
4       Jelly Fish Selection    cat food    img4    $1.99
5       Bananas                 fruit       img5    $2.84
6       Celery                  vegetables  img6    $5.99
7       Apple                   fruit       img7    $0.95
8       Pineapple Juice         Soft drink  img8    $0.99
9       Oranges                 fruit       img9    $1.99
10      Cabbage                 vegetables  img10   $0.99
11      Suicide Squad           dvd         img11   $12.99

The final result should look like this from the query

id      item                    type        image   price
==========================================================
11      Suicide Squad           dvd         img11   $12.99
10      Cabbage                 vegetables  img10   $0.99
9       Oranges                 fruit       img9    $1.99
8       Pineapple Juice         Soft drink  img8    $0.99
4       Jelly Fish Selection    cat food    img4    $1.99
3       Meaty Strips Chicken    dog food    img3    $2.99

I have tried various mysql queries, but to no avail

Upvotes: 0

Views: 33

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

A typical method uses a correlated subquery:

select n.*
from nunca n
where n.id = (select max(n2.id) from nunca n2 where n2.type = n.type);

This will choose the last row of each type.

Upvotes: 1

levdal
levdal

Reputation: 26

select * from t where 
id in(select max(t.id) id from t group by t.type) 
order by id desc

I guess there can be window or join alternatives but this one seemed fitting for this purpose.

Upvotes: 1

Related Questions