CODEWITHSUNDEEP
sqlpostgresql
John Smith
John Smith

Reputation: 6269

Get top values from two columns

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

Answers (3)

percy
percy

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

murison
murison

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

bgoldst
bgoldst

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

Related Questions