Try this:
DECLARE @ct INT
SELECT @ct = COUNT(DISTINCT category)
FROM @products
SET @ct = @ct * 4
SELECT T.*
FROM (
SELECT productID, producteName, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY productID DESC) AS Number
FROM products
) AS T
WHERE T.Number < 5
[EDIT 2]
Now, it must work!
I tested it on below example:
DECLARE @products TABLE (productID INT IDENTITY(1,1), producteName VARCHAR(30), category VARCHAR(30))
INSERT INTO @products (producteName, category)
VALUES('Audi','cars')
INSERT INTO @products (producteName, category)
VALUES('Volkswagen','cars')
INSERT INTO @products (producteName, category)
VALUES('Citroen','cars')
INSERT INTO @products (producteName, category)
VALUES('Peugeot','cars')
INSERT INTO @products (producteName, category)
VALUES('Honda','cars')
INSERT INTO @products (producteName, category)
VALUES('Toyota','cars')
INSERT INTO @products (producteName, category)
VALUES('Yamaha','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Romet','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Zipp','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Kymco','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Router','motor scooter')
INSERT INTO @products (producteName, category)
VALUES('Zumico','motor scooter')
INSERT INTO @products(producteName, category)
VALUES('Fiat','caravan')
INSERT INTO @products (producteName, category)
VALUES('Skoda','caravan')
SELECT T.*
FROM (
SELECT productID, producteName, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY productID DESC) AS Number
FROM @products
) AS T
WHERE T.Number < 5
Result:
productID | producteName | category | Number |
---|
14 | Skoda | caravan | 1 |
13 | Fiat | caravan | 2 |
6 | Toyota | cars | 1 |
5 | Honda | cars | 2 |
4 | Peugeot | cars | 3 |
3 | Citroen | cars | 4 |
12 | Zumico | motor scooter | 1 |
11 | Router | motor scooter | 2 |
10 | Kymco | motor scooter | 3 |
9 | Zipp | motor scooter | 4 |
[/EDIT]
[EDIT 3]
Read my comments and follow the links.
This artcile[
^] is about how to emulate MS SQL ROW_NUMBER() function in MySQL queries, but there are some restrictions.
Quote:
Can we have this result in MySQL? Here are two ways to get it. First, with user variables:
SET @iprev=0, @jprev=0;
SELECT i, j, row_number
FROM (
SELECT j, @jprev := if(@iprev = i, @jprev+1, 1) AS row_number, @iprev := i AS i
FROM test
ORDER BY i,j
) AS tmp;
The second method uses a join and aggregation, but is correct only if there are no duplicate values of j
SELECT a.i, a.j, count(*) as row_number
FROM test a
JOIN test b ON a.i=b.i AND a.j >= b.j
GROUP BY a.i, a.j ;
More interesting example queries, you'll find here:
http://www.artfulsoftware.com/infotree/queries.php[
^]
[/EDIT]