Solved

Return row that has the MAX value of a column

Posted on 2011-02-15
4
699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

627 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