CODEWITHSUNDEEP
sqldatabasepostgresqlgreatest-n-per-group
membersound
membersound

Reputation: 86657

How to retrieve specific columns in a GROUP BY sql statement?

Table airports:

 id | from | to | price | photo | notes
 _______________________________________
  1 | LON  | JFK|  1000 |       | test
  2 | LON  | JFK|  2000 |       | test2

I want to retrieve the bestprice entry of all from-to combinations inside the database. I want to fetch the whole record that is minprice found, or at least specific tables.

The following works, BUT only gives me the 3 columns from, to, price. Not the whole entity.

SELECT from, to, min(price) FROM airports GROUP BY from, to

How would I have to adapt this?

Upvotes: 0

Views: 125

Answers (5)

user330315
user330315

Reputation:

This is typically done using window functions:

select id, "from", "to", price, photo, notes
from (
    select id, "from", "to", price, photo, notes
           min(price) over (partition by "from", "to") as min_price
    from the_table
) t
where price = min_price
order by id;

from is a reserved word and it's a bad idea to use that as a column name (not entirely sure about to)

To deal with "ties" (same values in from, to and price), you can use the dense_rank() function instead:

select id, "from", "to", price, photo, notes
from (
    select id, "from", "to", price, photo, notes
           dense_rank() over (partition by "from", "to" order by price) as price_rank
    from the_table
) t
where price_rank = 1
order by id;

Upvotes: 4

Joël Salamin
Joël Salamin

Reputation: 3576

If you want to get the entire data, here is a query that solve your problem:

SELECT A.*
FROM airports A
INNER JOIN (SELECT A2.fromhere
                 ,A2.tohere
                 ,MIN(A2.price) AS minprice
            FROM airports A2
            GROUP BY A2.fromhere, A2.tohere) T ON T.fromhere = A.fromhere
                                          AND T.tohere = A.tohere
                                          AND T.minprice = A.price

The jointure is used to get only the best prices for each couple fromhere/tohere.

Hope this will help you.

Upvotes: 0

CodeNewbie
CodeNewbie

Reputation: 2091

A very simple solution would be this. SQLFiddle here

SELECT * 
FROM airports 
WHERE (from_place, to_place, price) =
(SELECT from_place, to_place, min(price) 
FROM airports 
GROUP BY from_place, to_place);

Use SELECT * FROM ... since you want the whole entity.

Upvotes: 1

tully2003
tully2003

Reputation: 131

There isn't going to be a way to get a "whole entity" there could be many rows in your table that could contain the matching from + to + min price

For example if your table contains

id | from | to | price | photo | notes
 _______________________________________
  1 | LON  | JFK|  1000 |       | test
  2 | LON  | JFK|  2000 |       | test2
  3 | LON  | JFK|  5000 |       | test3
  4 | LON  | JFK|  2000 |       | test4
  5 | LON  | JFK|  1000 |       | test5

Then both rows 1 and 5 meet your criteria of from + to + min price.

You could write the query

SELECT id, from, to, price, photo, notes
FROM airports a
INNER JOIN (
    SELECT from, to, min(price) [price]
    FROM airports 
    GROUP BY from, to) sub
    ON sub.from = a.from
   AND sub.to = a.to
   AND sub.price = a.price

Which would get you the matching records.

Upvotes: 0

lijat
lijat

Reputation: 690

You can order the results and use distinct on to take the first result from each grouping

select distinct on (from,to) * from airports order by from,to,price asc;

the above query should work

Upvotes: 1

Related Questions