0

System

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg19.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Issue

Need return one max value from table for every product_id (1 product_id has 10 store_ids) based on available_count.

Sql

select
    product_id, store_id, max(available_count)
from
    j_product_store_availability
group by
    product_id,
    store_id,
    available_count
order by
    product_id,
    available_count desc,
    store_id
--limit 1

Result

product_id store_id available_count

130475  4   7
130475  1   4
130475  8   4
130475  2   3
130475  5   3
130475  7   3
130475  10  3
130475  3   2
130475  6   2
130475  9   0
130479  1   0
130479  2   0
130479  3   0
...

It returns rows for every store_id. Need to return just one max value based on available_count.

Expected result

130475  4   7
130479  1   0
(next product_id)
...

Thanks.

Solution (Laurenz solution)

SELECT DISTINCT ON (product_id)
product_id,
store_id,
max(available_count)
from
j_product_store_availability
--where store_id = '7'
group by
product_id,
store_id,
available_count
ORDER by
product_id,
available_count desc;
  • Have you tried to remove available_count from GROUP BY and ORDER BY clause? – McNets Sep 5 at 10:51
  • @McNets Yes, I tried. I tried group by product_id only, but it return SQL Error [42803]. Tried OVER (PARTITION BY ...), but not working too. – genderbee Sep 5 at 10:58
  • Your added solution is a needless downgrade from the now correct answer of Laurenz. Don't use max(available_count) and don't add GROUP BY. See: stackoverflow.com/a/7630564/939860 – Erwin Brandstetter Sep 5 at 16:02
0

If using a non-standard PostgreSQL construct is no problem, the easiest way would be

SELECT DISTINCT ON (product_id)
       product_id, store_id, available_count
FROM j_product_store_availability
ORDER BY product_id, available_count DESC, store_id;
  • Hi, still returns all rows for all store_ids. Needed just 1 row with product_id and max(available_count) from all stores store_id. – genderbee Sep 5 at 11:27
  • @All Why it is not working as in this example? w3resource.com/PostgreSQL/postgresql-max-function.php – genderbee Sep 5 at 11:30
  • Seems this is working. SELECT DISTINCT ON (product_id) product_id, store_id, max(available_count) FROM j_product_store_availability group by product_id, store_id, available_count ORDER BY product_id, available_count desc;. – genderbee Sep 5 at 11:47
  • Sorry, I have fixed the answer. – Laurenz Albe Sep 5 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.