[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

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

0
globalwm2
Asked:
globalwm2
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
globalwm2Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
globalwm2Author Commented:
Yes, got that working. Thanks for working with me on this!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now