CODEWITHSUNDEEP
sqlpostgresql
sparkyspider
sparkyspider

Reputation: 13519

How do I group by minimum values in a SQL query?

I'm having trouble with a SQL aggregate techniques. Consider the table below:

Image
-----
id             (auto generated image id)
property_id    (foreign key)
image_url      (text)
image_priority (int)

I'd like a list of image URLs (one image for each property) with the lowest priority. In other words, the most popular image each property has (most popular because it has the lowest priority).

property_id | id | url 
----------------------
          1 | 17 | awesome.png
          2 | 23 | best.png

Here is my failed attempt:

SELECT id, property_id, url
FROM image
group by property_id
having priority = min(priority)

Upvotes: 1

Views: 89

Answers (2)

klin
klin

Reputation: 121604

Use distinct on:

select distinct on (property_id) id, property_id, image_url
from image
order by property_id, image_priority;

For the documentation:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. (...) Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

Upvotes: 2

Hogan
Hogan

Reputation: 70523

In a sub-select you can get an increasing value with ROW_NUMBER() that restarts with every image_url and is ordered by priority. Then just select those with a value of 1.

select *
from (
   select I.*, 
          ROW_NUMBER() OVER (PARTITION BY image_url ORDER BY impage_priority ASC) as ord
   from Image as I
) sub
where ord = 1

Upvotes: 1

Related Questions