OraFAQ Forum: SQL & PL/SQL » RANK issue in SQL

Home » SQL & PL/SQL » SQL & PL/SQL » RANK issue in SQL

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

RANK issue in SQL [message #682201]

Thu, 08 October 2020 19:40

aj123tx
Messages: 2
Registered: October 2020

Junior Member

need to consider timestamp difference while calculating rank

need helpin code to compare createddate between records and while ranking 5 seconds timestamp difference need to be ignored

ex : 16:50:40 and 16:50:41 and 16:50:42 should have same rank

Report message to a moderator

Re: RANK issue in SQL [message #682204 is a reply to message #682201]

Fri, 09 October 2020 00:23

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: RANK issue in SQL [message #682206 is a reply to message #682201]

Fri, 09 October 2020 06:18

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

Senior Member

So rank by date truncated to 5 seconds.

WITH DATES AS (
               SELECT  SYSDATE + LEVEL / 24 / 60 / 60 DT
                 FROM  DUAL
                 CONNECT BY LEVEL <= 20
              )
SELECT  TO_CHAR(DT,'MM/DD/YYYY HH24:MI:SS') DT,
        DENSE_RANK() OVER(ORDER BY DT - MOD((DT - TRUNC(DT)) * 24 * 60 * 60,5) / 24 / 60 / 60) RNK
  FROM  DATES
  ORDER BY DATES.DT
/

DT                         RNK
------------------- ----------
10/09/2020 07:16:26          1
10/09/2020 07:16:27          1
10/09/2020 07:16:28          1
10/09/2020 07:16:29          1
10/09/2020 07:16:30          2
10/09/2020 07:16:31          2
10/09/2020 07:16:32          2
10/09/2020 07:16:33          2
10/09/2020 07:16:34          2
10/09/2020 07:16:35          3
10/09/2020 07:16:36          3

DT                         RNK
------------------- ----------
10/09/2020 07:16:37          3
10/09/2020 07:16:38          3
10/09/2020 07:16:39          3
10/09/2020 07:16:40          4
10/09/2020 07:16:41          4
10/09/2020 07:16:42          4
10/09/2020 07:16:43          4
10/09/2020 07:16:44          4
10/09/2020 07:16:45          5

20 rows selected.

SQL>

SY.

Report message to a moderator

Re: RANK issue in SQL [message #682220 is a reply to message #682206]

Sat, 10 October 2020 06:56

aj123tx
Messages: 2
Registered: October 2020

Junior Member

super thanks a lot solomon

Report message to a moderator

Previous Topic:	Improve efficiency of script
Next Topic:	REgexp_substr function to extract sub string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 12 07:42:25 CST 2025