OraFAQ Forum: SQL & PL/SQL » row

Home » SQL & PL/SQL » SQL & PL/SQL » row_number with duplicity (12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

row_number with duplicity [message #681751]

Wed, 26 August 2020 04:57

mape
Messages: 298
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

Report message to a moderator

Re: row_number with duplicity [message #681752 is a reply to message #681751]

Wed, 26 August 2020 05:26

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: row_number with duplicity [message #681753 is a reply to message #681751]

Wed, 26 August 2020 07:41

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

SELECT  *
  FROM  YOUR_TABLE
  ORDER BY CONTRACT,
           NVL(ENDDATETIME,STARTDATETIME),
           SIGN(COST) DESC,
           COST

SY.

Report message to a moderator

Re: row_number with duplicity [message #681754 is a reply to message #681753]

Wed, 26 August 2020 07:58

mape
Messages: 298
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 09:02:49 CDT 2023