can you help me in this query

Question

2.78/5 (3 votes)

See more:

Hi all

I have products table like this:

productID,producteName,category

now, I need to get last 4 products inserted into products table for every category
help me please

regards

Posted 25-Mar-13 9:41am

ocean99

Add a Solution

Comments

joshrduncan2012 25-Mar-13 15:46pm

Are you familiar with the T-SQL language?

4 solutions

Solution 4

Based on Maciej Los] excellent example in Solution 2[^] I'd do it by generating a list of all categories first and then work it from there:

SQL

SELECT t.category, DISTINCT p1.category
FROM @products AS p1 RIGHT JOIN (
    SELECT TOP(4) p2.productID, p2.category
    FROM @products AS p1
    ORDER BY p2.productID DESC
) AS t ON p1.category = t.category
ORDER BY p1.category ASC

Sorry that I can't try this as I don't have an instance of SQL Server 2008 Handy (because of the table type used in Maciej's example).

Regards,

— Manfred

Posted 25-Mar-13 22:33pm

Manfred Rudolf Bihy

Comments

gvprabu 26-Mar-13 4:50am

Are u checked the output of ur Query... Pls check

Maciej Los 26-Mar-13 4:51am

Nice solution ;)
+5!

gvprabu 26-Mar-13 4:53am

Hi Maciej Los,
the Solution is not correct

Solution 3

Hi,

Check the Solution.... with Solution 1 Sample Data

SQL

DECLARE @ct INT
SET @ct = 4
 
SELECT p.productID, p.producteName, p.category
FROM @products AS p 
INNER JOIN (SELECT productID, 
               ROW_NUMBER() OVER(PARTITION BY category ORDER BY productID DESC) 'RowNumber'
 	    FROM @products) T ON T.productID=p.productID AND T.RowNumber<= @ct

Posted 25-Mar-13 22:27pm

gvprabu

Updated 25-Mar-13 22:29pm

v2

Comments

Maciej Los 26-Mar-13 4:52am

Good job!
+5

Solution 1

check this link ...
tips in last lines .
Tip

or maybe you should try this :

SQL

SELECT   top 4 *
FROM         Orders order by RequiredDate desc

this query will select first 4 rows from your table that ordered by RequiredDate (RequiredDate is date of registered row and last row has maximum ) descendly !

Posted 25-Mar-13 9:48am

ali_heidari_

Updated 26-Mar-13 2:21am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-03-25T10:58:00

Try this:

SQL

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:

SQL

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:

SQL
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

SQL
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]