Home » SQL & PL/SQL » SQL & PL/SQL » row_number with duplicity (12.1.0.2.0)
row_number with duplicity [message #681751] Wed, 26 August 2020 04:57 Go to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I would like to get know how to write a right select with sorted records.

For instance I got a records:


contract   startdatetime             enddatetime                cost
358880810   13.07.2020 22:43:09      NULL                    -2.14
358880810   13.07.2020 22:43:09      13.07.2020 22:43:09     2.64805


And I need to put it in order by rule: when cost >0 then the first row and the rest

I tried it (see below) but not ok:
 select row_number() OVER( PARTITION BY contract ORDER BY  nvl(enddatetime, startdatetime) ,
      CASE    WHEN cost<0 then startdatetime
      ELSE enddatetime  END ASC)
Does anybody know how to write a right ??

Thanks

Regards
MArtin




Re: row_number with duplicity [message #681752 is a reply to message #681751] Wed, 26 August 2020 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is clear as mud.
Provide a test case with its result.

Re: row_number with duplicity [message #681753 is a reply to message #681751] Wed, 26 August 2020 07:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
SELECT  *
  FROM  YOUR_TABLE
  ORDER BY CONTRACT,
           NVL(ENDDATETIME,STARTDATETIME),
           SIGN(COST) DESC,
           COST
SY.
Re: row_number with duplicity [message #681754 is a reply to message #681753] Wed, 26 August 2020 07:58 Go to previous message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I tried to put this order into select
   select  row_number() OVER( PARTITION BY contract_key ORDER BY contract,  NVL(ENDDATETIME,STARTDATETIME), SIGN(COST) DESC, COST ) 
     from ...

And did right.
Thanks for help Smile

[Updated on: Wed, 26 August 2020 07:58]

Report message to a moderator

Previous Topic: Find out how long is the field or column
Next Topic: Future PriceCalculation based on 3 columns
Goto Forum:
  


Current Time: Fri May 14 11:52:28 CDT 2021