Why should I provide the items.id column for the group by clause?

Question

I want to return unique items based on condition, sorted by price asc. My request failed because Postgres wants to items.idbe present in the offer group by. If it includes a query, returns everything that matches the sentence where, which I don't want. Why do I need to include a column?

select items.*
from items
where product_id = 1 and items.status = 'in_stock'
group by condition /* , items.id returns everything */
order by items.price asc

| id | condition | price |
--------------------------
| 1 | new        | 9     |
| 2 | good       | 5     |
| 3 | good       | 3     |

I only need elements with identifiers 1 and 3.

Update: here is the fiddle using the answer below that still causes the error:

http://sqlfiddle.com/#!1/33786/2

Answer 1

, PostgreSQL , items ; , :

| id | condition | price |
--------------------------
|  1 | new       |     9 |
|  3 | good      |     3 |

:

| id | condition | price |
--------------------------
|  1 | new       |     9 |
|  2 | good      |     5 |

, - , ​​ MAX:

SELECT MAX(id) AS id,
       condition,
       MAX(price) AS price
  FROM items
 WHERE product_id = 1
   AND status = 'in_stock'
 GROUP BY condition
 ORDER BY price ASC

:

| id | condition | price |
--------------------------
|  1 | new       |     9 |
|  3 | good      |     5 |

( SQL, . MySQL, , , " , , , " [.)

Answer 2

SQL Fiddle

select *
from (
    select distinct on (cond)
        id, cond, price
    from items
    where product_id = 1 and items.status = 'in_stock'
    order by cond, price
) s
order by price

Answer 3

SQL , , MySQL, .

"cond = good" , "id" , "cond = good", ? id = 3 id = 2? , ? MySQL , , .

, , .

PostgreSQL , DISTINCT ON ..., . , . DISTINCT ON , .

SQL- , . , , , inner where, .

SELECT *
FROM (
  SELECT *, dense_rank() OVER w AS itemrank
  FROM items
  WHERE product_id = 1 AND items.status = 'in_stock'
  WINDOW w AS (PARTITION BY cond ORDER BY price ASC)
) ranked_items
WHERE itemrank = 1;

(http://sqlfiddle.com/#!1/33786/19)

SQL , :

SELECT *
FROM items INNER JOIN (
  SELECT cond, min(price) AS minprice
  FROM items
  WHERE product_id = 1 AND items.status = 'in_stock'
  GROUP BY cond
) minprices(cond, price)
ON (items.price = minprices.price AND items.cond = minprices.cond)
ORDER BY items.price;

DISTINCT ON, , .

, DISTINCT ON, . PostgreSQL.

, PostgreSQL , GROUP BY; . , cols, PK . , , , , .

, , , , , , GROUP BY .