Need different values for same column

Database
Enthusiast

Need different values for same column

Need suggestion on my query .

I have a query like this

SELECT
SUB.emp_id ,
SUB.VISIT_NUMBER ,
SUB.VISIT_DATETIME ,
SUB.VISIT_LENGTH ,
SUB.VIEW_LENGTH ,
COALESCE(ORD.ORDER_CHECKOUT,'N') AS ORDR_CHECKOUT,
COALESCE(ORD.ORDER_AMOUNT,0) AS ORDR_AMOUNT,
COALESCE(ORD.ORDER_NUM,'N') AS ORDER_NUM
,ROW_NUMBER() OVER( PARTITION BY SUB.emp_id ORDER BY VISIT_LENGTH ASC) RNK
FROM table_a sub
LEFT JOIN table_b ord ON
ORD.emp_id=SUB.emp_id AND SUB.VISIT_NUMBER=ORD.VISIT_NUMBER

result will be

VISIT_NUMBER VISIT_DATETIME VISIT_LENGTH VIEW_LENGTH ORDR_CHECKOUT ORDER_amount RNK
8.633.110 05/01/2011 20:58:50 6.862 13.384 Y 442.80 1
8.633.110 05/01/2011 20:58:50 6.862 13.384 Y 200.00 2

here visit_number ,visit_datetime ,visit_length ,view _length are same except the order amount .

i would like to change the query to give an output like this

VISIT_NUMBER VISIT_DATETIME VISIT_LENGTH VIEW_LENGTH ORDR_CHECKOUT ORDER_amount RNK
8.633.110 05/01/2011 20:58:50 6.862 13.384 Y 442.80 1
8.633.110 05/01/2011 20:58:50 6.862 0 Y 200.00 2

how can we do this by using rownumber .

any one can please help me its required urgent .

2 REPLIES
Enthusiast

Re: Need different values for same column

You can just use:

case when RNK=2 then 0 else SUB.VIEW_LENGTH end as VIEW_LENGTH,

instead of SUB.VIEW_LENGTH , in select clause
Enthusiast

Re: Need different values for same column

thank u