Sessions Sessions - 2 months ago 7
SQL Question

Find the min and max values from one column associated with a particular id from another column

Hi (im pretty new to SQl), I want to find the min and max caseno associated with the id and also be able to get the price associated with those caseno. My data from two tables looks like this:

table 1

Id |Caseno |Price
-----------------
10 |101 |5
10 |102 |6
10 |103 |7
11 |201 |5
11 |202 |6
12 |203 |5
12 |204 |6
12 |205 |7


table 2

Caseno |Price
----------------
101 |5
102 |6
103 |7
201 |5
202 |6
203 |5
204 |6
205 |7


i want my output to be the following:

Id |Caseno|Price
-----------------
10 |101 | 5
10 |103 | 7
11 |201 | 5
12 |203 | 5
12 |205 | 7


the query i used is below but its not giving me the right output:

select id, price, max(caseno) lowestcaseno ,min(caseno) highestcaseno
from table1 t join table2 a on (t.caseno=a.caseno)
group by id,price


Do i need to create sub-queries? if yes should i put them in from or where clause? Thank you and any help is much appreciated!

Answer

Your sample data has some issues, but most of it would seem to imply that you want to retain casinos having the maximum and minimum price for each Id group. If so, then one approach is to create a subquery to identify these casinos, and then use this subquery to select the correct full records in your original table.

SELECT c1.Id,
       c1.Caseno,
       c1.Price
FROM caseno c1
INNER JOIN
(
    SELECT Id,
           MIN(Price) AS minPrice,
           MAX(Price) AS maxPrice
    FROM caseno
    GROUP BY Id
) c2
    ON c1.Id = c2.Id AND
       (c1.Price = c2.minPrice OR
        c1.Price = c2.maxPrice)