Multiple subquery or joins help

1

Hi,

I have a single table which for example has widget info, the columns being

id, model, sides, m_id (material id)

I have for ex. sample data of

1, x1, 2, 1
2, x2, 4, 1
3, y1, 2, 1
4, y2, 4, 1
5, x3, 4, 2
6, x4, 6, 2

I want to find the models with the max no. of sides for each type of material. So the answer should be
x2
y2
x4

I have tried two sql queries both give me too much data. I've tried these two

SELECT model, sides,
m_id FROM widgets
WHERE sides
IN (

SELECT MAX( sides )
FROM widgets
GROUP BY m_id
)
AND sides IN (

SELECT DISTINCT
sides FROM widgets
)

I've also tried

SELECT pl.model, pl.sides,
pl.m_id FROM widgets pl
INNER JOIN
(

SELECT MAX( sides ) as maxsides
FROM widgets
GROUP BY widgets.m_id
) e
ON e.maxsides = pl.sides
INNER JOIN
(
SELECT DISTINCT
m_id FROM widgets
)
p
ON p.m_id = pl.m_id

both give me

x2 4 1
y2 4 1
x3 4 2
x4 6 2

when x3 shouldn't be in the results. I understand why it is since 4 is a max value of sides.

I've solved the problem by using php and using two queries, one to get distinct values of m_id and using that as a parameter for the other query but I would like to learn to do it with just sql.

I know I could probably use a stored procedure and a cursor but that seems overkill.

So any help correcting my query would be appreciated.

Thanks!

2

make use of the dense_rank() window function to get the rows with most sides

select *
from
(
    select *, rn = dense_rank() over (partition m_id by order by sides desc)
    from   widgets
) d
where d.rn = 1
1 Like