How to specified below value from base record query?-CodePudding

I have table like this and I need to specify below price than base price in table.

id   value   price   base
1     aaa     100    
2     bbb     200    
3     ccc     300    Yes
4     aaa     200    Yes
5     aaa     300
6     aaa     400

like this :

id   value   price   status
1     aaa     100     down 
2     bbb     200     
3     ccc     300     base
4     aaa     200     base
5     aaa     300     up
6     aaa     400     up

With this code I can get max and min price value:

SELECT
    min(price) as min_price
    , max(price) as max_price
FROM 
(
    select *
    From MyTable
) tmp;

CodePudding user response：

If I understand the question correctly, the following statement is a possible solution to your problem:

Table:

SELECT *
INTO MyTable
FROM (VALUES
   (1, 'aaa', 100, NULL),
   (2, 'bbb', 200, NULL),
   (3, 'ccc', 300, 'Yes'),
   (4, 'aaa', 200, 'Yes'),
   (5, 'aaa', 300, NULL),
   (6, 'aaa', 400, NULL)
) v (id, value, price, base)

Statement:

SELECT 
   id,
   value,
   price,
   base,
   CASE
      WHEN base = 'Yes' THEN 'base'
      WHEN price < MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'down'
      WHEN price > MAX(CASE WHEN base = 'Yes' THEN price END) OVER (PARTITION BY value) THEN 'up'
   END AS status
FROM MyTable
ORDER BY id

Result:

id value price base status
--------------------------
1  aaa   100        down
2  bbb   200    
3  ccc   300   Yes  base
4  aaa   200   Yes  base
5  aaa   300        up
6  aaa   400        up