Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Return row that has the MAX value of a column

Posted on 2011-02-15
4
Medium Priority
?
702 Views
Last Modified: 2013-12-19
I wish to return the top row based on CategoryID. Currently the code returns more than one line (more than one category). I'm only interested in the row where the max value of CategoryID.

Here is what I have so far:
SELECT p.ProductID,
  UL.URL,
  p.UPC,
  p.EDPNO,
  p.ProductDefinitionID,
  e.Manufacturer,
  p.SKU,
  p.SKU2,
  p.ActualCost,
  p.DisplayPrice,
  p.CartPrice,
  p.Availability,
  p.MACSFlags,
  p.MACSStatus,
  p.InStockCount,
  p.DueInCount,
  p.BackOrderCount,
  p.GRScore,
  p.alwaysshowasinstock,
  p.autoinstockallowed,
  p.autoweightallowed,
  cpd.CategoryID,
  e.ImageSource,
  p.CreatedDate,
  p.ModDate,
  p.FirstArrivalDate,
  p.ExpectedStockDate,
  p.ReqShippingTypeID,
  p.ShippingIncludedID,
  p.Weight,
  -- C.categoryid,
  c.CategoryName,
  p.PriceDropAmount,
  p.PriceDropDate,
  p.ProductName,
  p.MANUFACTURERPARTNUM,
  p.OracleStatus,
  p.OracleTemplate,
  p.OracleUOM,
  p.ISBGOOD,
  p.isarchived,
  regexp_replace(p.sku, '\(.*\)','')  AS skunoparen,
  regexp_replace(p.sku2, '\(.*\)','') AS sku2noparen
FROM Products.Categories@prdweb c ,
  Products.Categories_ProductDefinitions@prdweb cpd,
  SiteSupport.Endeca_Other@prdweb e ,
  Products.Products@prdweb p,
  (SELECT P.PRODUCTID, 
  C.CATEGORYID,  -- Added  02.15.2011
     REPLACE(REPLACE( REPLACE(REPLACE(REPLACE('http://www.my.com/product/'
    || m.Company
    || '/'
    ||regexp_replace( NVL(p.Sku2,p.Sku),'( *[[:punct:]])','-')
    || '/'
    ||regexp_replace( NVL(c.AltName, c.CategoryName),'( *[[:punct:]])','-')
    || '/_/R-'
    || p.ProductId, ' ','-'),'(',''),')',''),'---','-'),'--','-') URL,
    p.Sku
  FROM PRODUCTS.Products@prdweb p
  JOIN
    (SELECT ProductDefinitionId,
      ManufacturerId
    FROM PRODUCTS.ProductDefinitions@prdweb
    ) pd
  ON p.ProductDefinitionId = pd.ProductDefinitionId
  JOIN
    (SELECT ManufacturerId, Company FROM PRODUCTS.Manufacturers@prdweb
    ) m
  ON pd.ManufacturerId = m.ManufacturerId
  JOIN
    (SELECT ProductDefinitionId,
      categoryId
    FROM PRODUCTS.Categories_ProductDefinitions@prdweb
    ) cpd
  ON cpd.ProductDefinitionId = pd.ProductDefinitionId
  JOIN PRODUCTS.Categories@prdweb c
  ON cpd.CategoryId = c.CategoryId
  ) UL
WHERE c.CategoryID          = cpd.CategoryID
AND e.ProductID             = p.ProductID
AND cpd.ProductDefinitionID = p.ProductDefinitionID
AND P.PRODUCTID             = UL.PRODUCTID
AND c.CATEGORYID    =  UL.CATEGORYID    -- Added 02.15.2011
AND cpd.CategoryID         <>262
and ul.url like '%R-103914%'
ORDER BY C.CATEGORYID DESC

Open in new window

0
Comment
Question by:globalwm2
  • 2
  • 2
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34902087
On mobile so can't go through all your code but try something like:

Select --all your columns
From (
Select-- your columns
, Row_number() over(order by category_id desc) myrownum

)
Where myrownum=1;
0
 

Author Comment

by:globalwm2
ID: 34902124
Thanks, I got that to work in my script - now I need to remove the filter for my test item:

and ul.url like '%R-103914%'

and have it work for all PRODUCTID values so I have only the PRODUCTID returned that has the max value of CATEGORYID ?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 total points
ID: 34902171
Look ayt adding a 'partition by column_name' in the OVER before the order by

Row_number() over(partition by productid order by categoryid desc) myrownum
0
 

Author Comment

by:globalwm2
ID: 34902231
Yes, got that working. Thanks for working with me on this!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question