Home » SQL & PL/SQL » SQL & PL/SQL » RANK issue in SQL
RANK issue in SQL [message #682201] Thu, 08 October 2020 19:40 Go to next message
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


Re: RANK issue in SQL [message #682204 is a reply to message #682201] Fri, 09 October 2020 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: RANK issue in SQL [message #682206 is a reply to message #682201] Fri, 09 October 2020 06:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
icon14.gif  Re: RANK issue in SQL [message #682220 is a reply to message #682206] Sat, 10 October 2020 06:56 Go to previous message
aj123tx
Messages: 2
Registered: October 2020
Junior Member
super thanks a lot solomon
Previous Topic: Improve efficiency of script
Next Topic: REgexp_substr function to extract sub string
Goto Forum:
  


Current Time: Thu Mar 28 05:40:09 CDT 2024