asked on

Display higher value between to numbers

I have a table with an Item Number and a Price, the table contains the same item number with different prices I want to display the item with the higher price so that I can delete it and keep the Item with the Lower price.

SELECT        SSGPROMO, ITEMNMBR, CUSTNMBR, STRTDATE, ENDDATE, SSG_Customer_Price, MRKDNAMT, PRICELVL, UOFM
FROM            PROMO_TEMP
WHERE        (ITEMNMBR IN ('AGUA1E092110', 'AGUA1E097704', 'AGUA1E452110', 'AGUA1E802110', 'LCS10877', 'LCS10907', 'LCS10915', 'PLT97069'))
ORDER BY ITEMNMBR

Open in new window

minnirok

Try using a subquery to pull the max price relative to each of your promo/item number combinations (assuming they are essentially operating as a composite primary key for this data).

SELECT    pt.SSGPROMO, pt.ITEMNMBR, pt.CUSTNMBR, pt.STRTDATE, pt.ENDDATE, pt.SSG_Customer_Price, pt.MRKDNAMT, pt.PRICELVL, pt.UOFM
FROM    PROMO_TEMP pt
WHERE    pt.ITEMNMBR IN ('AGUA1E092110', 'AGUA1E097704', 'AGUA1E452110', 'AGUA1E802110', 'LCS10877', 'LCS10907', 'LCS10915', 'PLT97069') AND pt.SSG_CustomerPrice = (SELECT MAX(SSG_Customer_Price) FROM PROMO_TEMP WHERE SSG_PROMO = pt.SSG_PROMO AND ITEMNMBR = pt.ITEMNMBR) 
ORDER BY    pt.ITEMNMBR

Open in new window

Ryan Chong

or try this:

Select * 
from
(

SELECT SSGPROMO, ITEMNMBR, CUSTNMBR, STRTDATE, ENDDATE, SSG_Customer_Price, MRKDNAMT, PRICELVL, UOFM,
Row_Number() over (Partition by ITEMNMBR order by SSG_Customer_Price) idx
FROM PROMO_TEMP
WHERE (ITEMNMBR IN ('AGUA1E092110', 'AGUA1E097704', 'AGUA1E452110', 'AGUA1E802110', 'LCS10877', 'LCS10907', 'LCS10915', 'PLT97069'))

) a
Where a.idx = 1
ORDER BY a.ITEMNMBR

Open in new window

for ref:

How to use ROW_NUMBER() to enumerate and partition records in SQL Server
http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R

Vitor Montalvão

If the price is the only column that distinguish them, then it's more easy and fast to use the MAX() function:

SELECT  SSGPROMO, ITEMNMBR, CUSTNMBR, STRTDATE, ENDDATE, MRKDNAMT, PRICELVL, UOFM, MAX(SSG_Customer_Price)
FROM  PROMO_TEMP
WHERE  ITEMNMBR IN ('AGUA1E092110', 'AGUA1E097704', 'AGUA1E452110', 'AGUA1E802110', 'LCS10877', 'LCS10907', 'LCS10915', 'PLT97069')
GROUP BY SSGPROMO, ITEMNMBR, CUSTNMBR, STRTDATE, ENDDATE, MRKDNAMT, PRICELVL, UOFM
ORDER BY ITEMNMBR

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

a test was run, here is the detail

    CREATE TABLE PROMO_TEMP
        ([SSGPROMO] varchar(8), [ITEMNMBR] varchar(8), [CUSTNMBR] varchar(8), [STRTDATE] varchar(8)
        , [ENDDATE] varchar(7), [SSG_Customer_Price] decimal(12,3), [MRKDNAMT] varchar(8), [PRICELVL] varchar(8), [UOFM] varchar(4))
    ;
        
    INSERT INTO PROMO_TEMP
        ([SSGPROMO], [ITEMNMBR], [CUSTNMBR], [STRTDATE], [ENDDATE], [SSG_Customer_Price], [MRKDNAMT], [PRICELVL], [UOFM])
    VALUES
        ('11305', 'A1001', '', '', '', 1.1, '', '', ''),
        ('11305', 'B1001', '', '', '', 2.1, '', '', ''),
        ('11305', 'B1001', '', '', '', 2.2, '', '', ''),
        ('11305', 'C1001', '', '', '', 3.1, '', '', ''),
        ('11305', 'C1001', '', '', '', 3.2, '', '', ''),
        ('11305', 'C1001', '', '', '', 3.3, '', '', '')
    ;
    
    with CTE as (
      select *
      , count(*)     over(partition by SSGPROMO,ITEMNMBR)  as cn
      , row_number() over(partition by SSGPROMO,ITEMNMBR 
                          order by SSG_Customer_Price ASC) as rn
      from PROMO_TEMP
      )
    delete from CTE 
    where rn > 1
    and cn > 1
    ;
    
    
**Query 1**:

    select
    *
    from PROMO_TEMP
    

**[Results][2]**:
    | SSGPROMO | ITEMNMBR | CUSTNMBR | STRTDATE | ENDDATE | SSG_Customer_Price | MRKDNAMT | PRICELVL | UOFM |
    |----------|----------|----------|----------|---------|--------------------|----------|----------|------|
    |    11305 |    A1001 |          |          |         |                1.1 |          |          |      |
    |    11305 |    B1001 |          |          |         |                2.1 |          |          |      |
    |    11305 |    C1001 |          |          |         |                3.1 |          |          |      |

  [1]: http://sqlfiddle.com/#!3/2031c/1

Open in new window

Fred Webb

ASKER

Thanks to all who answered but Paul Maxwell's Solution worked best because it not only gave me the higher of the 2 values but found the the duplicates as well.