Link to home
Start Free TrialLog in
Avatar of globalwm2
globalwm2Flag for United States of America

asked on

Return row that has the MAX value of a column

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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;
Avatar of globalwm2

ASKER

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 ?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, got that working. Thanks for working with me on this!