# How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row

I have a query where I JOIN 4 weekly "summary" tables, and SELECT a MIN and a MAX of units FROM each table. This would give me the highest/lowest total units sold for, and I'm pulling an average from another table.

``````    SELECT
L4.TotalUnits / 4   AS AvgMvmt,
MIN(Ago.TotalUnits) AS LowMvmt,
MAX(Ago.TotalUnits) AS HighMvmt
FROM

RTG_LOOKUP_LAST4 AS L4 INNER JOIN
(
SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
) AS Ago
ON Ago.UCC12UPCCode = L4.UCC12UPCCode
WHERE
L4.UCC12UPCCode = '01254601144'
GROUP BY
L4.TotalUnits
``````

Now I need to add two more columns, `HighDate` and `LowDate`. These columns represent the date for the week that sold the highest/lowest units.

The `Ago` table looks like this:

``````UCC12UPCCode  TotalUnits  Date
------------- ----------- ----------
01254601144   90          2018-04-14
01254601144   98          2018-05-05
01254601144   107         2018-04-21
01254601144   132         2018-04-28
``````

How would pull the `Date` column from the correct table so my end results looks like below:

``````LowMvmt     HighMvmt    LowDate    HighDate
----------- ----------- ---------- ----------
90          132         2018-04-14 2018-04-28
``````
• I mean, just use `MIN(Date)` and `MAX(Date)`?. (you also need to stop grouping by the `TotalUnits`) May 8, 2018 at 18:13
• `MIN/MAX(Date)` will return just that, min/max date, not the date that corresponds to `MIN(Ago.TotalUnits)` (if the first 2 total units, `90` and `98` were swapped, `MIN(date)` will not be correct.). I also have to group by unless i remove `L4.TotalUnits / 4 AS AvgMvmt`, which i need May 8, 2018 at 18:18
• You could try providing an SQL Fiddle with sample data and your desired result - your question is quite complex -- help us to help you! :-) May 8, 2018 at 18:49
• @Vérace see edit May 8, 2018 at 18:59

As a another option

``````WITH Ago AS (
SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

UNION

SELECT  UCC12UPCCode, TotalUnits, Date
FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
),
Ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank,
ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
FROM Ago
)
SELECT L4.UCC12UPCCode,
MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;
``````

I've done this using a CTE query as I find it easier to follow and explain.

The first CTE is just your `Ago` data.

The second CTE ranks each row of the `Ago` data using `ROW_NUMBER` based on the ordering of the `TotalUnits`. I have include the `Date` in the order so that if there are duplicate `TotalUnits` the first occurrence will be picked.

In the final query we are using `CASE` statements to pivot the data and aggregating the result for each `UCC12UPCCode` with `MAX` to create a single row for each `UCC12UPCCode`.

This query can be run over multiple `UCC12UPCCode`'s

A similar solution as @MickyT:

``````select upc
, max(case when asc_order = 1 then totalunits end) as min_units
, max(case when desc_order = 1 then totalunits end) as max_units
, max(case when asc_order = 1 then date end) as min_date
, max(case when desc_order = 1 then date end) as max_date
from (
SELECT row_number() over (order by TotalUnits) as asc_order
, row_number() over (order by TotalUnits desc) as desc_order
, upc
, TotalUnits
, Date
FROM (
SELECT  *   FROM    #RTG_Lookup2_4_Ago
UNION
SELECT  *   FROM    #RTG_Lookup2_3_Ago
UNION
SELECT  *       FROM    #RTG_Lookup2_2_Ago
UNION
SELECT  *       FROM    #RTG_Lookup2_Last1
) AS Ago
) as x
where 1 in (asc_order, desc_order)
group by upc;
``````

I took a different approach - thanks for the fiddle BTW. I think that you should consider changing your schema - you appear to be doing `UNION`s between a series of tables with different dates. This leads to a strange result

``````Low   High      Low date     High Date
90    132    2018-04-14    2018-04-28
``````

To me, it makes no sense to combine the low sale and date with the high sale and date - it makes much more sense in a relational theory to have this

``````Sale          Date        Volume
90         2018-04-14      Low
132         2018-04-28     High
``````

To tackle this I did the following (see my own fiddle here):

``````CREATE TABLE movement (share_sale INTEGER, share_code VARCHAR(20), mvt_date DATETIME);

INSERT INTO movement VALUES (100, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (150, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (137, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (112, '01254601144', '2018-05-08');
INSERT INTO movement VALUES (190, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (260, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (121, '01254601144', '2018-05-07');
INSERT INTO movement VALUES (165, '01254601144', '2018-05-07');
INSERT INTO movement VALUES ( 95, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (239, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (120, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (111, '01254601144', '2018-05-06');
INSERT INTO movement VALUES (222, '01254601144', '2018-05-06');
INSERT INTO movement VALUES ( 86, '01254601144', '2018-05-06');
INSERT INTO movement VALUES ( 75, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 45, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 98, '01254601144', '2018-05-05');
INSERT INTO movement VALUES (120, '01254601144', '2018-05-05');
INSERT INTO movement VALUES ( 67, '01254601144', '2018-05-05');
``````

And then I ran this query:

``````WITH cte1 AS
(
SELECT MIN(share_sale) AS min_sale, mvt_date AS min_mvt_date
FROM movement
GROUP BY mvt_date
),
cte2 AS
(
SELECT MAX(share_sale) AS max_sale, mvt_date AS max_mvt_date
FROM movement
GROUP BY mvt_date
),
cte3 AS
(
SELECT * FROM cte1
JOIN
(
SELECT * FROM cte2
) AS tab
ON cte1.min_mvt_date = tab.max_mvt_date
)
SELECT min_sale, max_sale, max_mvt_date AS mvt_date FROM cte3
``````

Result:

``````min_sale  max_sale     date:
45       120     05/05/2018 00:00:00
86       239     06/05/2018 00:00:00
121       260     07/05/2018 00:00:00
100       150     08/05/2018 00:00:00
``````

Now - on a given day, I give you (with my simulated data) the lowest sale and the highest sale - to me, this makes more sense than putting two different dates in with 2 different figures. If there isn't a sale every day, then maybe something would have to be done with `NULL`s. Just a few thoughts.

• If it helps, this is sales data. we cant "predict" which week will sell more/less. the 4 tables are weekly summaries of that weeks sales. In this situation, i need to know which of the 4 weeks sold the most/least and what that date is. The column `Volume` wouldnt help in this case (or atleast i dont think it would unless we update that column prior to running the query, correct me if im wrong) May 8, 2018 at 20:56
• Have a week_ending column so. The actual metric doesn't matter that much as long as you're comparing like with like on a weekly basis. But I don't see why you have tables for the last 4 weeks. Tables these days can easily hold hundreds of millions of records - I'd amalgamate if I were you - unless you're Walmart :-) May 8, 2018 at 21:16

I think you can use `corelated subquery` to get your desired result

``````SELECT
MIN(Ago.TotalUnits) AS LowMvmt,
MAX(Ago.TotalUnits) AS HighMvmt,
(select Date
from #TempAgo as Ago2
where min(Ago.totalunits) = ago2.totalunits) as lowdate,
(select Date
from #TempAgo as Ago2
where max(Ago.totalunits) = ago2.totalunits) as highdate
FROM
#TempAgo as Ago
``````