Strange Behaviour with Order by Limit Postgres12

Asked Mar 30 '20 at 09:12

Active Mar 30 '20 at 09:12

Viewed 36 times

I have a query like:

SELECT ed.Entity_Data_sur_id
FROM ucc.Entity_Data ed
WHERE   ed.entity_id = 1
ORDER BY timestamp DESC

which returns:

entity_data_sur_id
10021351
10020351
10019351
10018351
10017351
10016351
10015351

but when adding limit 1

SELECT ed.Entity_Data_sur_id
FROM ucc.Entity_Data ed
WHERE   ed.entity_id = 1
ORDER BY timestamp DESC limit 1

I am getting

entity_data_sur_id
6001351

Is this a bug in PostgreSQL12?

asked Mar 30 '20 at 09:12

mahmoud alorfaly

What data type is `entity_data_sur_id` – Mar 30 '20 at 09:13
2

Are `timestamp` values alone sufficient to *uniquely* order the rows? I.e. that no two rows share the same `timestamp` value? If not, you've given the system freedom to be arbitrary in the rows it selects among equal values. – Damien_The_Unbeliever Mar 30 '20 at 09:20
entity_data_sur_id is a serial data type – mahmoud alorfaly Mar 30 '20 at 09:20
@Damien_The_Unbeliever You are right, i just figured that out. I am closing this question – mahmoud alorfaly Mar 30 '20 at 09:21
What is result from: `select * from ucc.Entity_Data where timestamp = (select max(timestamp) from ucc.Entity_Data where entity_id = 1);` – Oto Shavadze Mar 30 '20 at 10:28

0 Answers0