CODEWITHSUNDEEP
sqlteradata
madsthiru
madsthiru

Reputation: 51

How to fetch the values for a column based on existing columns

My requirement is to fetch the data in the below format: If i have the same value for COL1 , then the lowest value of COL2 should get populated for all the rows and corresponding value of COL3 has to be populated (This can be hardcoded as it will not change) and value of COL4 corresponding to the lowest value of COL2 should get populated for all the rows.(This cannot be hardcoded)

Existing data in the table

SELECT COL1 , COL2 , COL3 , COL4 FROM TABLE1 

COL1  COL2  COL3  COL4
1234  01    ABC   YYY
1234  02    DEF   ZZZ

My expected output is:

COL1  COL2  COL3  COL4
1234  01    ABC   YYY
1234  01    ABC   YYY

I tried the below query and I got the output for COL2 and COL3 but for COL4 I am not getting.

SELECT
  COL1,
  MIN(COL2) OVER (PARTITION BY COL1)     AS COL2NEW,
  CASE WHEN COL2NEW = 1 THEN 'ABC'
       WHEN COL2NEW = 2 THEN 'DEF' END   AS COL3NEW,
  COL4
FROM
  TABLE1

Kindly guide me to get the appropriate output for COL4.

Upvotes: 0

Views: 196

Answers (1)

MatBailie
MatBailie

Reputation: 86798

Your question is quite ambiguous, but I think FIRST_VALUE() is the analytic function you're looking for...

SELECT
  COL1,
  MIN(COL2) OVER (PARTITION BY COL1)     AS COL2NEW,
  CASE WHEN COL2NEW = 1 THEN 'ABC'
       WHEN COL2NEW = 2 THEN 'DEF' END   AS COL3NEW,
  FIRST_VALUE(COL4) OVER (PARTITION BY COL1
                              ORDER BY COL2
                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                         )
                            AS COL4NEW
FROM
  TABLE1

It takes the first value of COL4 after sorting the partition by COL2. This means that all rows in the partition get the same value in COL4NEW, and that value is from the input row with the lowest value in COL2.

Upvotes: 1

Related Questions