Can you get a different column from a row with a MIN or MAX value?

user_1958160 0
Viewed 51

I'm building an application with millions of rows, so I'm trying to avoid JOIN whenever possible. I have a table like this:

ID        category  value_1   value_2
1         1         2.2432    5.4321
2         2         6.5423    5.1203
3         1         8.8324    7.4938
4         2         0.4823    9.8244
5         2         7.2456    3.1278
6         1         1.9348    4.4421

I'm trying to retrieve value_1 from the row with the lowest ID and value_2 from the row with the highest ID while grouped by category, like this:

category  value_1   value_2
1         2.2432    4.4421
2         6.5423    3.1278

Is this possible in an effective way while avoiding constructs like string operations and JOIN?

Thank you!

mysql
2 Answers

Try this:

SELECT 
    category, 
    (
        SELECT t2.value1
        FROM table1 t2
        WHERE t2.id = MIN(t1.id)
    ) as value1,
    (
        SELECT t3.value2
        FROM table1 t3
        WHERE t3.id = MAX(t1.id)
    ) as value2
FROM
    table1 t1
GROUP BY
    category
;

Create and fill table:

CREATE TABLE `table1` (
  `id` INT NOT NULL,
  `category` INT NULL,
  `value1` DOUBLE NULL,
  `value2` DOUBLE NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO table1 VALUES
(1, 1, 2.2432, 5.4321),
(2, 2, 6.5423, 5.1203),
(3, 1, 8.8324, 7.4938),
(4, 2, 0.4823, 9.8244),
(5, 2, 7.2456, 3.1278),
(6, 1, 1.9348, 4.4421);

Output:

1   2.2432  4.4421
2   6.5423  3.1278
user_17679120

One approach which avoids joins is to use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY ID)      rn_min,
              ROW_NUMBER() OVER (PARTITION BY category ORDER BY ID DESC) rn_max
    FROM yourTable
)

SELECT
    category,
    MAX(CASE WHEN rn_min = 1 THEN value_1 END) AS value_1,
    MAX(CASE WHEN rn_max = 1 THEN value_2 END) AS value_2
FROM cte
GROUP BY
    category;

screen capture from demo link below

Demo

Edit:

The above query should benefit from the following index:

CREATE INDEX idx ON yourTable (category, ID);

This should substantially speed up the row number operations.

user_18632290
Related