CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyflask-sqlalchemy
Faisal
Faisal

Reputation: 408

DISTINCT ON combined with ORDER BY returns wrong results

results = Tracking.query. \
    filter(Tracking.time >= datetime.strptime(date_1, '%d-%m-%Y'),).\
    filter(Tracking.time <= datetime.strptime(date_2, '%d-%m-%Y')).\
    order_by(Tracking.device_id, desc(func.date(Tracking.time))). \
    distinct(Tracking.device_id, func.date(Tracking.time))

I have query like above, but resulting the only true result in the early date like this

[
    {
        "device_id": 1,
        "device_serial_number": "213123",
        "id": 31419,
        "latitude": "-6.1486514",
        "longitude": "106.894557",
        "time": "Mon, 19 Mar 2018 14:35:20 GMT",
        "video_id": 1,
        "video_name": "dubstep",
        "video_path": "http://localhost:5000/public/files/DubstepBirdOriginal5SecVideo.mp4"
    },
    {
        "device_id": 1,
        "device_serial_number": "213123",
        "id": 13000,
        "latitude": "-6.1509214",
        "longitude": "106.89634459999999",
        "time": "Sat, 17 Mar 2018 00:00:05 GMT",
        "video_id": 1,
        "video_name": "dubstep",
        "video_path": "http://localhost:5000/public/files/DubstepBirdOriginal5SecVideo.mp4"
    },
    {
        "device_id": 1,
        "device_serial_number": "213123",
        "id": 12988,
        "latitude": "-6.151098",
        "longitude": "106.89603483333333",
        "time": "Fri, 16 Mar 2018 23:59:43 GMT",
        "video_id": 1,
        "video_name": "dubstep",
        "video_path": "http://localhost:5000/public/files/DubstepBirdOriginal5SecVideo.mp4"
    }
]

16 Mar has the correct result, but others do not, as you can see in the image below: 17 and 19 of March have later data that must be shown.

enter image description here

If my query is wrong, how can I fix this query? I try with order by and group by and it same result.

Upvotes: 1

Views: 1412

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52929

You're truncating the timestamp Tracking.time to a date when ordering, so rows with the same device id and date part will be in unspecified order relative to each other. DISTINCT ON will then pick what ever row happens to be first.

You'll have to add the time to your ordering one way or another. A quick and dirty solution is to just add the timestamp as 3rd sorting expression:

results = Tracking.query. \
    filter(Tracking.time >= datetime.strptime(date_1, '%d-%m-%Y')).\
    filter(Tracking.time <= datetime.strptime(date_2, '%d-%m-%Y')).\
    order_by(Tracking.device_id,
             func.date(Tracking.time).desc(),
             Tracking.time.desc()).\
    distinct(Tracking.device_id, func.date(Tracking.time))

Alternatively you could cast the timestamp to a time:

from sqlalchemy import Time

results = Tracking.query. \
    ...
    order_by(Tracking.device_id,
             func.date(Tracking.time).desc(),
             Tracking.time.cast(Time).desc()).\

Upvotes: 2

Related Questions