Solved

Return row that has the MAX value of a column

Posted on 2011-02-15
4
697 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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 39
How to Gracefuly recover in Racle stored procedure 1 34
Oracle DB monitor SW 21 60
Oracle 12c Default Isolation Level 17 41
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

828 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