Reputation: 6269
Lets say I have a table like this:
id | peru | usa
1 20 10
2 5 100
3 1 5
How can I get the top values from peru
and usa
as well as the spefic ids. So that I get as result:
usa_id: 2 | usa: 100 | peru_id: 1 | peru: 20
Is this possible In one query? Or do I have to do two ORDER BY
querys?
Im using postgresql
Upvotes: 0
Views: 105
Reputation: 1118
You can consider using MAX aggregate function in conjunction with ARRAY type. Check this out:
CREATE TEMPORARY TABLE _test(
id integer primary key,
peru integer not null,
usa integer not null
);
INSERT INTO _test(id, peru, usa)
VALUES
(1,20,10),
(2,5,100),
(3,1,5);
SELECT MAX(ARRAY[peru, id]) AS max_peru, MAX(array[usa, id]) AS max_usa FROM _test;
SELECT x.max_peru[1] AS peru, x.max_peru[2] AS peru_id, x.max_usa[1]
AS usa, x.max_usa[2] AS usa_id FROM (
SELECT MAX(array[peru, id]) AS max_peru,
MAX(array[usa, id]) AS max_usa FROM _test ) as x;
Upvotes: 1
Reputation: 3995
SELECT
t1.id as peru_id, t1.peru
, t2.id as usa_id, t2.usa
FROM tab1 t1, tab1 t2
ORDER BY t1.peru desc, t2.usa desc
limit 1
http://sqlfiddle.com/#!15/0c12f/6
As basicly what this does is a simple carthesian product - I guess that performance WILL be poor for large datasets.
on the fiddle it took 196ms for a 1k rows table. On 10k rows table - sqlFiddle hung up.
Upvotes: 1
Reputation: 35324
You can do this with some subqueries and a cross join:
select
u.id usa_id,
u.usa,
p.id peru_id,
p.peru
from
(select id, usa from mytable where usa=(select max(usa) from mytable) order by id limit 1) u
cross join (select id, peru from mytable where peru=(select max(peru) from mytable) order by id limit 1) p
;
In the case that there are multiple rows with the same max value (for usa or peru, independently), this solution will select the one with the lowest id (I've assumed that id is unique).
Upvotes: 1