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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

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

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

and ORDER BY AND GROUP BY may be able to pull it off, although a subquery would make it so much easier.
bg
Forum Contributor
Posts: 157
Joined: Fri Sep 12, 2003 11:01 am

Post 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.
Post Reply