CODEWITHSUNDEEP
sqlpostgresql
sagar_c_k
sagar_c_k

Reputation: 123

SQL query not returning all possible rows

I am writing a simple SQL query to get the latest records for every customerid. My SQL query -

SELECT sub.customerid, sub.device_count, sub.date_time FROM(
    SELECT customerid, cast(device_count as INT) as device_count, date_time,
        RANK() OVER
            (
                PARTITION by customerid
                ORDER BY date_time desc
            ) AS rownum 
    FROM tableA ) sub 
JOIN tableB d on sub.customerid = d.customerid 
WHERE rownum = 1

Sample data:

customerid device_count date_time
A 3573 2021-07-26 02:15:09-05:00
A 4 2021-07-26 02:15:13-05:00
A 16988 2021-07-26 02:15:13-05:00
A 20696 2021-07-26 02:15:13-05:00
A 24655 2021-07-26 02:15:13-05:00
A 10000 2021-07-25 02:15:13-05:00
A 2000 2021-07-25 02:15:13-05:00

What I need is:

customerid device_count date_time
A 4 2021-07-26 02:15:13-05:00
A 16988 2021-07-26 02:15:13-05:00
A 20696 2021-07-26 02:15:13-05:00
A 24655 2021-07-26 02:15:13-05:00

But what I get after executing the above query is :

customerid device_count date_time
A 4 2021-07-26 02:15:13-05:00
A 16988 2021-07-26 02:15:13-05:00

I am not sure why the remaining rows are left out even though they have the same timestamp. Any help would be appreciated!

Upvotes: 0

Views: 264

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

The timestamp might have milliseconds that you are are not seeing.

You can truncate to the nearest second:

SELECT sub.customerid, sub.device_count, sub.date_time
FROM (SELECT customerid, cast(device_count as INT) as device_count, date_time,
             RANK() OVER (PARTITION by customerid ORDER BY DATE_TRUNC(second, date_time) desc) AS rownum 
      FROM tableA
     ) sub JOIN
     tableB d 
     ON sub.customerid = d.customerid 
WHERE rownum = 1

Upvotes: 1

Related Questions