From: Martin Gainty
Date: July 14 2010 2:49pm
Subject: RE: Select w/ group by question
List-Archive: http://lists.mysql.com/mysql/222206
Message-Id: <BLU142-W34E0B4CD3BBC68A7B26FDDAEBA0@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_eba7ca44-d002-4eb3-95b6-1e5704e0c772_"

--_eba7ca44-d002-4eb3-95b6-1e5704e0c772_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable




=20


> Date: Wed=2C 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smullen27@stripped
> To: mysql@stripped
>=20
> I'm having trouble formulating a query to gather the following data. I ca=
n
> do this via a script=2C but now it is more or less just bothering me if t=
here
> is an easy/efficient way to gather the following data from a single query=
.
>=20
> Example Tables
>=20
>=20
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
>=20
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
>=20
>=20
> I would like to obtain the least cost of each product type and its
> associated vendor.
>=20
> So...I would like to see a result similiar to the following:
>=20
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm n=
ot
> really concerned with which vendor is chosen in the result set here.)
>=20
>=20
> If I do:=20

select a.type=2C min(a.cost)=2C a.vendor_id=2C b.vendor_name from
products a join=20

vendors b=20

on a.vendor_id =3D b.vendor_id=20

group by a.type=2Ca.vendor_id=2C b.vendor_name=20

=20

all rows are returned because the type/vendor_id/vendor_name are unique amo=
ngst each row.=20

=20

If you remove the vendor_id and vendor_name from the group by=2C=20

=20

select a.type=2C min(a.cost)=2C a.vendor_id=2Cb.vendor_name from
products a join vendors b=20

on a.vendor_id =3D b.vendor_id=20

group by a.type=3B

=20

you get a single row with the lowest cost for each product=2C=20

but the vendor_id's and vendor_name's are incorrect because you are not gro=
uping by them.
>=20
> Is there a way to do this from a single query. I know I can concat things
> together and imbed a select in my where clause to get the result I want=
=2C but
> this is horribly inefficient.
> My real tables have somewhere around 30 million rows in them.

MG>put ascending indexes on vendor_id and vendor_name columns

MG>reorg the tables so the rows will be in vendor_name (within vendor_id) o=
rder

=20

MG>reselect

MG>select a.type=2C min(a.cost)=2C a.vendor_id=2Cb.vendor_name from
MG>products a join vendors b=20

MG>on a.vendor_id =3D b.vendor_id=20

MG>order by a.type=3B


>=20
> Thanks
>=20
> Scott

 		 	   		 =20
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmulticalendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_eba7ca44-d002-4eb3-95b6-1e5704e0c772_--