select max, min values from two tables

Question

I have two tables. Differ in that an archive is a table and the other holds the current record. These are the tables recording sales in the company. In both we have among other fields: id, name, price of sale. I need to select from both tables, the highest and lowest price for a given name. I tried to do with the query:

select name, max (price_of_sale), min (price_of_sale)
from wapzby
union
select name, max (price_of_sale), min (price_of_sale)
from wpzby
order by name

but such an inquiry draws me two records - one of the current table, one table archival. I want to chose a name for the smallest and the largest price immediately from both tables. How do I get this query?

What SQL engine is this? To my eye you are missing a GROUP BY (to aggregate MIN and MAX around each distinct `name`) and a WHERE clause (to restrict to just one `name`). — pilcrow, Oct 24 '11 at 14:31

Amy B · Answer 1 · 2013-07-06T14:05:32.010

Here's two options (MSSql compliant)

Note: UNION ALL will combine the sets without eliminating duplicates. That's a much simpler behavior than UNION.

SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
FROM
(
    SELECT Name, Price_Of_Sale
    FROM wapzby
    UNION ALL
    SELECT Name, Price_Of_Sale
    FROM wpzby
) as subQuery
GROUP BY Name
ORDER BY Name

This one figures out the max and min from each table before combining the set - it may be more performant to do it this way.

SELECT Name, MAX(MaxPrice) as MaxPrice, MIN(MinPrice) as MinPrice
FROM
(
    SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
    FROM wapzby
    GROUP BY Name
    UNION ALL
    SELECT Name, MAX(Price_Of_Sale) as MaxPrice, MIN(Price_Of_Sale) as MinPrice
    FROM wpzby
    GROUP BY Name
) as subQuery
GROUP BY Name
ORDER BY Name

score 8 · Answer 2 · answered Oct 24 '11 at 14:31

In SQL Server you could use a subquery:

SELECT  [name],
        MAX([price_of_sale]) AS [MAX price_of_sale],
        MIN([price_of_sale]) AS [MIN price_of_sale]
FROM (
    SELECT  [name],
            [price_of_sale]
    FROM    [dbo].[wapzby]
    UNION
    SELECT  [name],
            [price_of_sale]
    FROM    [dbo].[wpzby]
) u
GROUP BY [name]
ORDER BY [name]

score 2 · Answer 3 · answered Oct 24 '11 at 14:27

2

Is this more like what you want?

SELECT 
    a.name,
    MAX (a.price_of_sale), 
    MIN (a.price_of_sale) ,  
    b.name, 
    MAX (b.price_of_sale), 
    MIN (b.price_of_sale) 
FROM 
    wapzby a, 
    wpzby b
ORDER BY 
    a.name

It's untested but should return all your records on one row without the need for a union

answered Oct 24 '11 at 14:27

Purplegoldfish

5,268
9
39
59

1

If there is beer in a, and cheese in b... He'll get a row in the result about beer and cheese. I don't think cartesian join is what he wants. – Amy B Oct 24 '11 at 14:36
Best. answer. ever – Amit Jan 25 '19 at 12:34

score 1 · Answer 4 · edited May 28 '16 at 15:24

1

SELECT MAX(value) FROM tabl1 UNION SELECT MAX(value) FROM tabl2;
SELECT MIN(value) FROM tabl1 UNION SELECT MIN(value) FROM tabl2;

edited May 28 '16 at 15:24

Ianthe the Duke of Nukem

1,721
2
20
37

answered May 28 '16 at 13:00

Karim Elshaweish

39
4

Perhaps it'll help if you describe what that sql does and format it for readability. – Ianthe the Duke of Nukem May 28 '16 at 13:12
your variant also shows 2 values while the question was how to get the single max and single min values. – Alexei Kaigorodov Jun 10 '20 at 08:40

score 0 · Answer 5 · answered Dec 16 '21 at 06:15

0

SELECT (SELECT MAX(value) FROM table1 WHERE trn_type='CSL' and till='TILL01') as summ, (SELECT MAX(value) FROM table2WHERE trn_type='CSL' and till='TILL01') as summ_hist

answered Dec 16 '21 at 06:15

user17690236

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 16 '21 at 08:11

select max, min values from two tables

5 Answers5

Linked