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!