From: Rick James
Date: October 11 2012 6:40pm
Subject: RE: column aliases in query
List-Archive: http://lists.mysql.com/mysql/228375
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF92884@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Some places allow aliases, some don't.  Some (GROUP BY, ORDER BY) even allo=
w ordinals.

For performance, the optimal index would be
    INDEX(factory_id, date)
and then do
       WHERE date >=3D "2012-10-11"
         AND date <  "2012-10-11" + INTERVAL 1 DAY


> -----Original Message-----
> From: Mark Haney [mailto:markh@stripped]
> Sent: Thursday, October 11, 2012 10:47 AM
> To: MySQL
> Subject: column aliases in query
>=20
> I know it's been a while since I wrote serious queries, but I'm sure I
> have done something like this before:
>=20
> SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id,
> machine.factory_id FROM events JOIN machine ON events.mach_id =3D
> machine.mach_id WHERE machine.factory_id =3D "1" AND vDate =3D "2012-10-1=
1"
>=20
> Where I've aliased the SUBSTR of the date and then used the alias in
> the WHERE clause of the query.  I'm getting an error message now, but
> I'm almost certain I've used that syntax before.  Am I missing
> something?
>=20
> --
>=20
> Mark Haney
> Software Developer/Consultant
> AB Emblem
> markh@stripped
> Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql