PostgreSQL – Sort by First Column with WHERE Condition

postgresqlsorting

I am using PostgreSQL 11.5 on Ubuntu

Issue

I have sql with joining tables and with where condition, which looks like this.

...

where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431',
'318417',

...

When I run sql.

copy(
select distinct on ("HOBIS")
-- s_product.product_id,
product_shop_id as "HOBIS",
product_name as "NAME",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019') as "URL",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
price_tax as "PRICE VAT",
case when price_rec > price_tax then concat(price_rec) end as "PRICE ORIGINAL"
from
s_product
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431'
)
and image_order = '0'
and s_pricelist_generated_lists.group_id = '1'

--order by "HOBIS"
) to stdout (format csv, quote '"');

Results are sorted by s_product.product_shop_id.

...

230041,"Interiérové dveře Klasik plné dub archico, 3D povrch - 60 cm L",https://eshop.unihobby.cz/stavba-dvere-interierove-dvere-dvere-klasik-plne-dub-archico-3d-povrch/136280p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/klasik-plne-dub-archico.jpg,990.00,1390.00
237002,Vinylová podlaha Kronostep - True Grit,https://eshop.unihobby.cz/stavba-podlahy-vinylove-podlahy-podlaha-vinylova-kronostep-true-grit-5-0mm/151328p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/237002-krono-original-krono-xonic-r018-true-grit-3.jpg.big.jpg,1058.00,1323.00
318417,Primalex Malvena na fasády 1 l,https://eshop.unihobby.cz/bydleni-barvy-a-laky-venkovni-barvy-primalex-malvena-na-fasady-1l/153358p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/318417-malvena-5l.jpg,199.00,
464431,Benzínová řetězová pila CSH46 Scheppach,https://eshop.unihobby.cz/zahrada-zahradni-technika-retezove-pily-benzinove-retezove-pily-pila-retezova-benzinova-csh46-scheppach/150162p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/464431-csh46-scheppach-diy-garten-titel-neu-na.jpg,3290.00,

...

But I want to have results as they are in where condition. Is possible to have results as they are in where condition? And why PG sort results when I have no sort condition in my sql?

Thanks.

Best Answer

You can do that if you change the way you pass the values to the WHERE clause. One way is to join to an unnested array and use the array index for sorting.

As you need a different sort order for the distinct on () you have to wrap the query in a derived table. The inner part uses an order by on the "distinct column". And the result can then be sorted by the array index.

select "HOBIS", "NAME", "URL", ... -- all columns but the `idx`
from (
  select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            ..., 
            x.idx
  ...
  from s_product
    join unnest(array['237002','230041','467173','464431']) with ordinality as x(shopid, idx) 
      on s_product.product_shop_id = x.shopid
    left join ...
    ...
  order by "HOBIS"
) t
order by t.idx

This behaves a bit different to an IN condition, so you have to make sure that every shop ID only appears once in the array.

And why PG sort results when I have no sort condition in my sql?

Postgres does not sort your results.

Without an order by clause, the database is free to return the rows in any order it wants. The "order" results from the different steps in the execution plan. If you see some perceived "order", then this probably stems from retrieving rows through an index.

But the "order" you see there is in no way guaranteed. It can change because rows have been changed or inserted into the table, or because the query optimizer chooses to use a different execution plan. Or because some other transaction is running a similar statement.

Related Question