CODEWITHSUNDEEP
sqlsql-serverdense-ranksql-order-by
Yokomoko
Yokomoko

Reputation: 137

DENSE_RANK() OVER (Order by UniqueIdentifer) issue

I'm struggling trying to get DENSE_RANK to do what I want it to do.

It is basically to create a unique invoice number based on a unique identifier, but it needs to go up in order based on the date/time of the invoice.

For example I need:

InvoiceNo                  TxnId                     TxnDate
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:01
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:02
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:03
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:04
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:05
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:06
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:07
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    02/01/2014 00:08
    2       8A5BCC36-8A70-4BE1-9FAB-A33BDD5BB78F    02/02/2014 00:09
    2       8A5BCC36-8A70-4BE1-9FAB-A33BDD5BB78F    02/02/2014 00:09
    3       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:10
    3       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:20
    3       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:21
    3       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:23

But what I get when using DENSE_RANK OVER (Order by TxnId) is:

InvoiceNo       TxnId                                 TxnDate
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:02
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:01
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:03
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:04
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:06
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:05
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    02/01/2014 00:08
    1       6C952E91-B888-4244-9079-14FBECAE0BA2    01/01/2014 00:07
    2       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:10
    2       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:21
    2       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:20
    2       83168B53-1647-4EB9-AF17-0B285EAA69B4    03/03/2014 00:23
    3       8A5BCC36-8A70-4BE1-9FAB-A33BDD5BB78F    02/02/2014 00:09
    3       8A5BCC36-8A70-4BE1-9FAB-A33BDD5BB78F    02/02/2014 00:09

If I do DENSE_RANK OVER(TxnId,TxnDate), it is a complete mess and doesn't do what I want either.

Any ideas guys? Am I even using the write function to do this? Any help appreciated :)

Upvotes: 3

Views: 621

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want:

select dense_rank() over (order by txnid, txndate)

Everything with the same transaction id and date will have the same value.

EDIT:

If you need to extract the date, then that depends on the database. It would look something like this. For Oracle:

select dense_rank() over (order by txnid, trunc(txndate))

For Postgres:

select dense_rank() over (order by txnid, date_trunc('day', txndate))

For SQL Server:

select dense_rank() over (order by txnid, cast(txndate as date))

EDIT II:

You want the transactions ordered by the earliest date. Get the earliest date and then do the dense_rank():

select dense_rank() over (order by txnmindate, txnid)
from (select t.*, min(txndate) over (partition by txnid) as txnmindate
      from table t
     ) t

Upvotes: 2

Related Questions