--- On Thu, 17/1/08, joebert jacaba <[EMAIL PROTECTED]> wrote: > From: joebert jacaba <[EMAIL PROTECTED]> > Subject: Re: [plug] Large MySQL table > To: "Philippine Linux Users' Group (PLUG) Technical Discussion List" > <[email protected]> > Date: Thursday, 17 January, 2008, 11:49 AM > my query should have an and on the where clause for > year(due_date)=year(now()). sorry. > > Common queries: > > 1. get all due dates of an account code before a specified > date
Index due_date and account SELECT due_date FROM table WHERE account=Account and due_date<Date where Account and Date could be a variable or a constant if you are building the SQL statement on the fly. > 2. get all due dates of an account code after a specified > date Still index due_date and account SELECT due_date FROM table WHERE account=Account and due_date>Date > 3. get the most recent due date or the next due date of an > account code Still index due_date and account Most recent: SELECT due_date FROM table WHERE account=Account and due_date<Date ORDER BY due_date DESCENDING LIMIT 1 Next due: SELECT due_date FROM table WHERE account=Account and due_date>Date ORDER BY due_date ASCENDING LIMIT 1 > 4. get the account details of an or number ??? > 5. get all due dates in a specified month year Still index due_date and account SELECT account, due_date FROM table WHERE due_date BETWEEN StartDate AND EndDate --- mike t. _________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List [email protected] (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
