asked on

Exclude Duplicates based on column value

I have a view SSG_CATALOG that exports data for our catalog with columns ITEMNMBR, ITEMDESC, DEALERPRICE, PRCLEVEL my problem is that because we used different price levels foreach item it creates a row for each item. For example Item ACCP2015BR1 has a price level of A, AAA, and B but the DEALERPRICE is the same for all 3 levels so I just want to return only the A price level if the DEALERPRICE for AAA and B = A.

David Todd

Hi,

My thought is a group by with the min of prclevel

ie
select
c.itemnmbr
, c.itemdesc
, c.dealerprice
, min( c.prclevel ) as PRCLEVEL
from dbo.SSG_CATALOG c
where
somewhereclause
group by
c.itemnmbr
, c.itemdesc
, c.dealerprice
order by
c.itemnmbr
, c.itemdesc
, c.dealerprice
;

HTH
David

Jim Horn

One way would be to use RANK() to order the PRICELEVEL values, grouped by ITEMNBR and DEALERPRICE, alphabetically (Assuming A is better than AAA is better than B), then just grab all the rows that sort first.

SELECT a.ITEMNMBR, a.ITEMDESC, a.DEALERPRICE, a.PRICELEVEL
FROM (
   SELECT ITEMNBR, ITEMDESC, DEALERPRICE, PRICELEVEL, 
      RANK() OVER (PARTITION BY ITEMNMBR, DEALERPRICE ORDER BY PRICELEVEL) as rank_order) a
WHERE a.rank_order = 1

Open in new window

David Todd

Hi,

I'd be interested in the execution plan costs - if there are major differences between these two approaches.

Regards
David

Fred Webb

ASKER

Thanks for the responses

David,
I get the following error from your suggestion
Msg 4145, Level 15, State 1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'group'.

Jim,
I cant even get yours to run I think the reference to the SSG_CATALOG is missing

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

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

Start Free Trial

David Todd

Hi,

Did you just cut and paste my answer, or did you read it? You'll need to edit the where clause (or delete it!)

Regards
David

Fred Webb

ASKER

David, My bad I did miss the WHERE clause.

Jim, thanks for fixing that, I knew the Reference to the table was missing so I added it

OK, so with using Jim's solution I get 25071 rows With David's I get 24974 a difference of 97 rows

Jim Horn

>OK, so with using Jim's solution I get 25071 rows With David's I get 24974 a difference of 97 rows
Since we don't have access to your data set, you will have to identify the 97 row difference and troubleshoot. Perhaps there are duplicate rows being returned, in which case you can replace SELECT with SELECT DISTINCT.

Fred Webb

ASKER

Jim,
I used DISTINCT but it only returned 1 row less, and I examined the results and they look good so I am going with your solution. Thanks to David for his solution also.