Page 1 of 1

$@#$ Stupid join, I need help with this query

Posted: Thu Aug 25, 2005 1:38 pm
by magicrobotmonkey
Ok, I have a setup where I have two tables, A and B. I am joining A and B and there are 5 B rows for every A so I'll get

Code: Select all

A1  B11
A1  B12
A1  B13
A1  B14
A1  B15
A2  B21
A2  B22
A2  B23  

and so on..
Now, what I need to do is return only the minium B for each row:

Code: Select all

A1 B11
A2 B21
A3 B31
But I can't just use MIN(B) and Group By A because I need to return the entire B row, and min only gives you the minimum for whichever field you give it. This seems like a simple thing to do, but I can't get it for the life of me.

Thanks.

Posted: Thu Aug 25, 2005 1:50 pm
by feyd
and ORDER BY AND GROUP BY may be able to pull it off, although a subquery would make it so much easier.

Posted: Fri Aug 26, 2005 9:33 am
by bg
Its hard to say what to do without knowing how the tables are relating to eachother and with what fields, but here is an idea

Code: Select all

SELECT distinct(a.value), min(b.value) FROM a
WHERE a.related_key = b.related_key
try something like that.