Question 418548 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Order by ASC doesn't return any item but order by DESC does

Any known explanation of this? Why Order By ASC doesn’t return any item but order by DESC doesn’t?

I am using postgres with pg_vector extension

ASC

DESC

SELECT
       langchain_pg_embedding.document,
       langchain_pg_embedding.embedding <=> (ARRAY [-0.021213598549366,  ...,           -0.03685509413480759])::vector AS distance
FROM langchain_pg_embedding
         JOIN langchain_pg_collection
              ON langchain_pg_embedding.collection_id = langchain_pg_collection.uuid
WHERE langchain_pg_embedding.collection_id = '3831f45b-ee1b-4009-afa4-c7b0e1aadda7'
ORDER BY distance DESC
LIMIT 1;

I am expecting in each query I will get one row (but different).

Tags:

2

Answers


  1. 0

    Normally, sorting only affects the order of the result set, not the content of the result set.

    If the data is not large, it is recommended to remove limit 1 and see if the total amount of data is correct.

    I feel that there is a sort order that causes the results to be null for the first few, and the query tool does not show it.

    Login or Signup to reply.
  2. 0

    This has occured due to the possibility of some rows or records having null value in the distance field. Since you are sorting on the distance column, there are two scenarios when sorting on a field where the values could be null.

    1. Order by field asc: By default, it sorts on the field(distance) with the null values last. i.e It sorts the non-null values first and then the null values after.

    2. Order by field desc: This is the opposite of the asc where the null values are sorted first before the non-null values by default.

    To get the behaviour to act the same in both scenarios, you need to include the keyword NULLS LAST or NULLS FIRST depending on your needs for both cases.

    SELECT
       langchain_pg_embedding.document,
       langchain_pg_embedding.embedding <=> (ARRAY [-0.021213598549366,  ...,           -0.03685509413480759])::vector AS distance
FROM langchain_pg_embedding
         JOIN langchain_pg_collection
              ON langchain_pg_embedding.collection_id = langchain_pg_collection.uuid
WHERE langchain_pg_embedding.collection_id = '3831f45b-ee1b-4009-afa4-c7b0e1aadda7'
ORDER BY distance DESC NULLS LAST
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search