1

I need to get the bottom N values from a table taking into account that multiple categories or types are inserted.

my table:

CREATE TABLE `Table1` (
    `name` VARCHAR(250) NOT NULL,
    `total` INT(11) NOT NULL,
    `type` INT(11) NOT NULL
)
ENGINE=InnoDB;

Some values:

|| *name*       || *total*  || *type*   ||

|| Clock        || 12       || 103      ||

|| Brief Case   || 21       || 103      ||

|| Pencil       || 34       || 103      ||

|| Lollypop     || 45       || 103      ||

|| Notebook     || 67       || 142      ||

|| Rubber Band  || 3        || 143      ||

|| Smartphone   || 1        || 143      ||

What I got so far:

SELECT name,total from Table1 ORDER BY TOTAL ASC LIMIT 3 where type=143;

but I need the same result for every category with one query.

Tried this:

SELECT name,total from Table1 ORDER BY TOTAL ASC LIMIT 3 GROUP BY type;

but this is wrong. As expected, I retrieve just 3 records from all records instead 3 from every category.

[possible duplicate] Mysql selecting (n) rows of each occurrance

Community
  • 1
  • 1
useRj
  • 1,232
  • 1
  • 9
  • 15
  • I don't think it's possible to do so with one mysql query, except if you know all types in advance (then you can make long and ugly UNION of all of them) – Ashalynd Feb 11 '14 at 10:56

1 Answers1

0

Try this :

SELECT name,total from Table1 as t1
WHERE t1.name in (SELECT t2.name FROM Table1 as t2 WHERE t1.type = t2.type ORDER BY t2.TOTAL ASC LIMIT 3)
Rida BENHAMMANE
  • 4,111
  • 1
  • 14
  • 25