CODEWITHSUNDEEP
sqlsql-server
Ramaq
Ramaq

Reputation: 33

Selection within table for new column

I have the table pricedata (see attached file). I want to add two extra columns to this table (MinPrice of Competitor and CompetitorID of MinPrice). I wrote a code to get the column MinPrice of Competitor, BUT I have no idea how to get second column, any help ????

Code:

select a.ValuationDate, a.shop, a.Itemcode, a.OwnPrice,
       a.[sales price competitor], a.[competitor ID], b.MinPrice
from [PriceTable] a
  inner join
      (select ValuationDate, Shop, ItemCode,
              min([sales price competitor]) as MinPrice
       FROM [PriceTable]
       group by ValuationDate, Shop, ItemCode) b
    on  a.ValuationDate = b.ValuationDate
    and a.Shop = b.Shop
    and a.ItemCode = b.ItemCode

Actual Table:

Actual Table

Required Table:

Required Table

Upvotes: 1

Views: 52

Answers (2)

Serg
Serg

Reputation: 22811

Look at (not tested). A common way to get the row with min/max value is SELECT TOP(1) .. ORDER BY .. DESC/ASC

select a.ValuationDate, a.shop, a.Itemcode, a.OwnPrice,
       a.[sales price competitor], a.[competitor ID], 
       c.MinPrice, c.[competitor ID]
from [PriceTable] a
cross apply
   (SELECT TOP(1) b.[competitor ID]
              b.[sales price competitor] as MinPrice
    FROM [PriceTable] b     
    WHERE a.ValuationDate = b.ValuationDate
    and a.Shop = b.Shop
    and a.ItemCode = b.ItemCode  
    ORDER BY b.[sales price competitor] DESC) c

Upvotes: 1

Sandesh
Sandesh

Reputation: 1044

Here you go. Hope this helps.

select 
    a.ValuationDate, 
    a.shop
    a.Itemcode, 
    a.OwnPrice, 
    a.[sales price competitor],
    a.[competitor ID],  
    b.MinPrice ,
    MINCOMPID.[competitor ID] AS 'CompetitorID of MinPrice'
from [PriceTable] a
inner join 
(
select ValuationDate, Shop, ItemCode, min([sales price competitor]) as MinPrice   FROM [PriceTable]
group by ValuationDate, Shop, ItemCode
) b
on a.ValuationDate=b.ValuationDate and a.Shop=b.Shop and a.ItemCode=b.ItemCode
INNER JOIN [PriceTable] AS MINCOMPID
ON MINCOMPID.ValuationDate=b.ValuationDate and MINCOMPID.Shop=b.Shop and MINCOMPID.ItemCode=b.ItemCode AND MINCOMPID.[sales price competitor]=b.MinPrice

Upvotes: 1

Related Questions