Home » SQL & PL/SQL » SQL & PL/SQL » product and category query, please help (sql server, oracle , mySQL)
product and category query, please help [message #580551] Mon, 25 March 2013 15:51 Go to next message
ocean9
Messages: 17
Registered: February 2011
Location: syria
Junior Member

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 in SQL not PL-SQL

regards
Re: product and category query, please help [message #580557 is a reply to message #580551] Mon, 25 March 2013 16:38 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What are "last 4 products"?
Re: product and category query, please help [message #580583 is a reply to message #580557] Tue, 26 March 2013 01:06 Go to previous messageGo to next message
ramoradba
Messages: 2453
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please provide some test data to help you in a better way.

Sriram Sanka
Re: product and category query, please help [message #580585 is a reply to message #580551] Tue, 26 March 2013 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: product and category query, please help [message #580677 is a reply to message #580551] Tue, 26 March 2013 14:58 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
It depends on the structure of your table, that's why you were asked to put a test case along with the information, but acording to the structure of the table Products you gave us
productID,producteName,category, the column ProductID must be a secuence, so the final query would be:

select productID,producteName,category
from products
where productID between (select max(productID)-3 from products) and (select max(productID) from products);

Regards,
Steve.
Re: product and category query, please help [message #580678 is a reply to message #580677] Tue, 26 March 2013 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure scanning 3 times the table is the best way?

Regards
Michel
Re: product and category query, please help [message #580680 is a reply to message #580678] Tue, 26 March 2013 15:25 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
I don't know whether is the best way or not. Since the user haven't provided the information you've requested is the easyest way to solve his problem. We didn't discussed the performance of the query, I just made it "on the fly"

Regards,
Steve.
Re: product and category query, please help [message #580681 is a reply to message #580680] Tue, 26 March 2013 15:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is surely not the first way I'd think of.

Regards
Michel
Re: product and category query, please help [message #580682 is a reply to message #580680] Tue, 26 March 2013 16:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, assuming, as you did, the last 4 products are those with the 4 greatest productid:
with maxid as (select max(productID) maxid from products)
select productID,producteName,category
from products, maxid
where productID between maxid-3 and maxid
/

with 
  data as (
    select productID,producteName,category,
           row_number() over (order by productID desc) rn
    from products
  )
select productID,producteName,category
from data
where rn <= 4
/

Regards
Michel
Re: product and category query, please help [message #580699 is a reply to message #580682] Wed, 27 March 2013 01:32 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Please try this.


WITH 
  data AS (
    SELECT productID,producteName,category,
	rank() over (PARTITION BY CATEGORY ORDER BY ROWNUM  DESC) Rn
            FROM products
  )
SELECT productID,producteName,category
FROM data
WHERE rn <= 4



Regards,
Paules
Re: product and category query, please help [message #580701 is a reply to message #580699] Wed, 27 March 2013 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This wrong how rownum can determine the last inserted products?
Maybe (with the same assumption than in my previous post):
with 
  data as (
    select productID,producteName,category,
           row_number() over (partition by category order by productID desc) rn
    from products
  )
select productID,producteName,category
from data
where rn <= 4


Regards
Michel
Re: product and category query, please help [message #580714 is a reply to message #580701] Wed, 27 March 2013 04:13 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Dear Michel,

row_number() is a function?

Regards,
Paules
Re: product and category query, please help [message #580716 is a reply to message #580714] Wed, 27 March 2013 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 11069
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes, look it up in the documentation if you don't know what it does.
Re: product and category query, please help [message #580717 is a reply to message #580716] Wed, 27 March 2013 04:19 Go to previous message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Thanks , I Got it.

Regards,
Paules
Previous Topic: ORA-00936: Expression missing
Next Topic: Returning first rank after aggregating
Goto Forum:
  


Current Time: Tue Oct 21 07:16:11 CDT 2014

Total time taken to generate the page: 0.05224 seconds