Re: Query question: select * from table where id in (1,2,3) order by date uses FILESORT
I agree with Brent on what MySQL is doing ... are you seeing poor performance with this query? If so, you might evaluate whether adding an index on your 'post_date' column improves things, as MySQL may be able to sort and thus LIMIT more quickly (using index in RAM rather than reading off disk). It may not make a difference unless your result set from the "WHERE ID IN ()" portion is reasonably large, though. Dan Brent Baisley wrote: MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records. You can throw and EXPLAIN in front of the query to see what mysql is going to do. - Original Message - From: "Peter Van Dijck" <[EMAIL PROTECTED]> To: Sent: Tuesday, June 20, 2006 3:08 AM Subject: Query question: select * from table where id in (1,2,3) order by date uses FILESORT Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question: select * from table where id in (1,2,3) order by date uses FILESORT
MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records. You can throw and EXPLAIN in front of the query to see what mysql is going to do. - Original Message - From: "Peter Van Dijck" <[EMAIL PROTECTED]> To: Sent: Tuesday, June 20, 2006 3:08 AM Subject: Query question: select * from table where id in (1,2,3) order by date uses FILESORT Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question: select * from table where id in (1,2,3) order by date uses FILESORT
Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER by date: reverse order
write: order by date desc DeRyl - Original Message - From: "Jerry Swanson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 05, 2004 8:00 PM Subject: ORDER by date: reverse order I want to sort by date but the last date appears first. How to write such query? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER by date: reverse order
[snip] I want to sort by date but the last date appears first. How to write such query? [/snip] RTFM ORDER BY theDate DESC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER by date: reverse order
I want to sort by date but the last date appears first. How to write such query? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by date
Xavier, select ID, description, if (ifnull(date1, 20201231) < ifnull(date2, 20201231), ifnull(date1, 20201231), ifnull(date2, 20201231)) as date from MyTable order by date I didn't test it, but it should work. Any questions? don't hesitate to ask. Best regards, Mikhail. - Original Message - From: "Xavier NOPRE" <[EMAIL PROTECTED]> To: "Mailing list MySql" <[EMAIL PROTECTED]> Sent: Tuesday, September 03, 2002 11:18 AM Subject: Order by date > Hi, > > I have a query from tables that give me records with two date "date1" and > "date2". How can I order the records by the most recent date (between the > two date) for each records ? Example: > > Data: > > IDdescriptiondate1date2 > 1 aaa09/03/02 09/05/02 > 2 bbb 09/04/02 > 3 ccc 09/02/02 09/10/02 > > Result: > > IDdescriptiondate > 3 ccc 09/02/02 > 1 aaa09/03/02 > 2 bbb09/04/02 > > Thanks > > Xavier > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Order by date
Hi, I have a query from tables that give me records with two date "date1" and "date2". How can I order the records by the most recent date (between the two date) for each records ? Example: Data: IDdescriptiondate1date2 1 aaa09/03/02 09/05/02 2 bbb 09/04/02 3 ccc 09/02/02 09/10/02 Result: IDdescriptiondate 3 ccc 09/02/02 1 aaa09/03/02 2 bbb09/04/02 Thanks Xavier - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
Hi.
On Mon 2002-07-29 at 14:24:54 -0400, [EMAIL PROTECTED] wrote:
>
> That's a really weird problem. Perhaps someone else could reproduce it if
> it's a bug in the most recent MySQL.
It's not a bug.
> I can think of an immediate way to solve it; use the type (datetime?),
> which is really a string 20020729. I don't see how this could be sorted
> incorrectly, but check your types.
>
> On Mon, 29 Jul 2002, julian haffegee wrote:
>
> > Date: Mon, 29 Jul 2002 17:59:00 +0100
> > From: julian haffegee <[EMAIL PROTECTED]>
> > To: MySQL General List <[EMAIL PROTECTED]>
> > Subject: order by date
> >
> > This must be a regularly appearing problem, which is why i'm surprised I
> > can't find a webpage on it..
> >
> > I have
> >
> > $result = mysql_query("SELECT title, url, description, author,
> > date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE
> > documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC");
> >
> > Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th
> > etc rather than 31st, 30th and so on.
Of course, you sort by "date" which is aliased to
"DATE_FORMAT(date,'%D %M %Y')", which is a usual text, which has not
reason to be sorted numerically, as you would expected it to.
The solution is simply to sort by the original date value, which is
only possible, if you change the alias from "date" to something else,
e.g. "pretty_date" (btw, it's generally considered bad style to
overload existing names).
SELECTtitle, url, description, author,
DATE_FORMAT(date,'%D %M %Y') AS pretty_date
FROM documents_tbl AS d, url_tbl AS u
WHERE d.title_id = u.url_id
ORDER BY date DESC
If you really want to sort by day first, and month next, you would
have to make a seperate sorting column (which you ignore in your
application, of course):
SELECTtitle, url, description, author,
DATE_FORMAT(date,'%D %M %Y') AS pretty_date,
DATE_FORMAT(date,'%d%m%Y') AS order_date
FROM documents_tbl AS d, url_tbl AS u
WHERE d.title_id = u.url_id
ORDER BY order_date DESC
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
Hi,
Change the alias to date_format(date,'%D %M %Y') AS
mydate, so you are sure that you sort by the date
field not my the formatted date. Be aware that if you
sort my the formatted string, MySql is going to treat
it as a string and therefore sort it as a string.
"11th July 2002" < "2st July 2002" if we compare them
as strings but 07-11-2002 > 07-02-2002 if we compare
them as dates.
Bye and Good Luck!
--- julian haffegee <[EMAIL PROTECTED]> wrote:
> This must be a regularly appearing problem, which is
> why i'm surprised I
> can't find a webpage on it..
>
> I have
>
> $result = mysql_query("SELECT title, url,
> description, author,
> date_format(date,'%D %M %Y') AS date FROM
> documents_tbl, url_tbl WHERE
> documents_tbl.title_id = url_tbl.url_id ORDER BY
> date DESC");
>
> Everything works fine, but the ORDER BY bit sorts
> all dates as 9th, 8th 7th
> etc rather than 31st, 30th and so on.
>
> can anyone tell me where to look to read all about
> it.
>
> Thanks Jules
>
>
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date
That's a really weird problem. Perhaps someone else could reproduce it if
it's a bug in the most recent MySQL.
I can think of an immediate way to solve it; use the type (datetime?),
which is really a string 20020729. I don't see how this could be sorted
incorrectly, but check your types.
On Mon, 29 Jul 2002, julian haffegee wrote:
> Date: Mon, 29 Jul 2002 17:59:00 +0100
> From: julian haffegee <[EMAIL PROTECTED]>
> To: MySQL General List <[EMAIL PROTECTED]>
> Subject: order by date
>
> This must be a regularly appearing problem, which is why i'm surprised I
> can't find a webpage on it..
>
> I have
>
> $result = mysql_query("SELECT title, url, description, author,
> date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE
> documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC");
>
> Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th
> etc rather than 31st, 30th and so on.
>
> can anyone tell me where to look to read all about it.
>
> Thanks Jules
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
order by date
This must be a regularly appearing problem, which is why i'm surprised I
can't find a webpage on it..
I have
$result = mysql_query("SELECT title, url, description, author,
date_format(date,'%D %M %Y') AS date FROM documents_tbl, url_tbl WHERE
documents_tbl.title_id = url_tbl.url_id ORDER BY date DESC");
Everything works fine, but the ORDER BY bit sorts all dates as 9th, 8th 7th
etc rather than 31st, 30th and so on.
can anyone tell me where to look to read all about it.
Thanks Jules
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY date DESC
Never mind ... I made a mistake in the SQL statement !!! :-) Son Nguyen --- Son Nguyen <[EMAIL PROTECTED]> wrote: > mysql> desc albums; > +-+-+--+-+++ > | Field | Type| Null | Key | Default| Extra | > +-+-+--+-+++ > | album_ID| int(11) | | PRI | NULL | auto_increment | > | songs | text| | ||| > | singers | text| | ||| > | from_date | date| | | -00-00 || > | to_date | date| | | -00-00 || > +-+-+--+-+++ > 5 rows in set (0.01 sec) > > ==> I want to create a select statement to view the latest record > according to the "to_date" field > > SELECT songs, singers FROM albums WHERE ORDER BY to_date DESC LIMIT 1 > > The ORDER BY statement take in the unsigned field to evaluate the > case > ... but to_date is a "date" type ... What should I write for that SQL > statement ? > > > > Son Nguyen > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ORDER BY date DESC
mysql> desc albums; +-+-+--+-+++ | Field | Type| Null | Key | Default| Extra | +-+-+--+-+++ | album_ID| int(11) | | PRI | NULL | auto_increment | | songs | text| | ||| | singers | text| | ||| | from_date | date| | | -00-00 || | to_date | date| | | -00-00 || +-+-+--+-+++ 5 rows in set (0.01 sec) ==> I want to create a select statement to view the latest record according to the "to_date" field SELECT songs, singers FROM albums WHERE ORDER BY to_date DESC LIMIT 1 The ORDER BY statement take in the unsigned field to evaluate the case ... but to_date is a "date" type ... What should I write for that SQL statement ? Son Nguyen __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Victoria Reznichenko declared > NW> and here is my select: > > NW> $qry="SELECT id, module, week, "; > NW> $qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, "; > > The result of DATE_FORMAT() function is a string. So, in ORDER BY > clause is used string 'date'. That is why you get "wrong" sorting. Use > another alias for your date column, it should help you, i.e.: Yes! How strange, the penny dropped whilst I was making a cup of tea and I was sure I had it. Now I /know/ I do. Many thanks Victoria :-) sql - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE81+GXHpvrrTa6L5oRAuMXAJ0clmtEYrYscsNlNgCktrN5AlPDfwCffgWS bfCr8rkBNl4YwtNo7TAbbWY= =/rKf -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
Quoting Nick Wilson ([EMAIL PROTECTED]): > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > > * and then Victoria Reznichenko declared > > NW> I've been searching for a solution to the following: > > > > NW> Is there an easy way to order by date ( - MM - DD), at the moment my > > NW> results are coming back with the *day* ordered but not the whole date > > NW> like this: > > > > NW> 02 02 2002 > > NW> 03 03 2005 > > NW> 18 02 2003 ... > and here is my select: > > $qry="SELECT id, module, week, "; > $qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, "; > $qry.="ektione, title, teacher_id "; > $qry.="FROM $this->table "; > $qry.="WHERE module = '$module' "; > $qry.="ORDER BY date ASC"; Get rid of the "as date" from your sql query. I think that should make it work the way you want. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Jay Blanchard declared > If the date is in the format above you can select a substring > +++ > | field1 | rDate | > +++ > | 1 | 2002-03-01 | > | 2 | 2002-03-03 | > | 3 | 2002-03-05 | > | 4 | 2002-03-07 | > | 5 | 2002-03-11 | > +++ > > mysql> select substring_index(rDate, "-", -1) as Day > -> from tblDate1 > -> order by Day; Thanks, but as I understand it this should not be needed as it is a regular date column and mysql should sort it for me right? - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE819tsHpvrrTa6L5oRAkUjAJ0WsLUYpOkKmWAwvn08m8ewQ6cgMQCcDxoq iOgEUaj2XvG3trF/nWqQXt4= =Uh1r -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by date
Nick,
Tuesday, May 07, 2002, 4:01:09 PM, you wrote:
>> What is your column type? Why you don't use ORDER BY your_date_column
>> in the SELECT statement? It should work, but I can say you exactly
>> because I don't know your table structure. The other causes of wrong
>> result might be that something is not correct in your SQL statement.
NW> Right. here is the db schema: Feel free to berate me if I'm missing the
NW> obvious :-)
NW> CREATE TABLE iaadiplom_timetable
NW> (
NW> id INT NOT NULL AUTO_INCREMENT,
NW> module ENUM('0', '1', '2', '3', '4', '5'),
NW> week VARCHAR(255) NULL,
NW> date DATE NOT NULL,
NW> ektione VARCHAR(255) NULL,
NW> title TEXT NULL,
NW> teacher_id INT NULL,
NW> PRIMARY KEY(id)
NW> )
NW> and here is my select:
NW> $qry="SELECT id, module, week, ";
NW> $qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, ";
The result of DATE_FORMAT() function is a string. So, in ORDER BY
clause is used string 'date'. That is why you get "wrong" sorting. Use
another alias for your date column, it should help you, i.e.:
$qry="SELECT id, module, week, ";
$qry.="DATE_FORMAT(date, \"%d %m %Y\") as mydate, ";
[skip]
$qry.="ORDER BY date ASC";
NW> $qry.="ektione, title, teacher_id ";
NW> $qry.="FROM $this->table ";
NW> $qry.="WHERE module = '$module' ";
NW> $qry.="ORDER BY date ASC";
NW> Thanks very much for the help!
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Order by date
[snip] >> NW> Is there an easy way to order by date ( - MM - DD), at the moment my >> NW> results are coming back with the *day* ordered but not the whole date >> NW> like this: >> >> NW> 02 02 2002 >> NW> 03 03 2005 >> NW> 18 02 2003 >> >> NW> See what I mean? >> [/snip] If the date is in the format above you can select a substring +++ | field1 | rDate | +++ | 1 | 2002-03-01 | | 2 | 2002-03-03 | | 3 | 2002-03-05 | | 4 | 2002-03-07 | | 5 | 2002-03-11 | +++ mysql> select substring_index(rDate, "-", -1) as Day -> from tblDate1 -> order by Day; +--+ | Day | +--+ | 01 | | 03 | | 05 | | 07 | | 11 | +--+ If you want to get fancy then you could CONCAT the whole date back together, with the day in the lead position, month in second, year in third. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
Hello!
May be you are using data as text field or date is being stored in 3 separate columns.
Tell me if I am right. You will
need to store the date as date data type and will be automatically sorted.
Regards
~~~
Tahir Ata Barry
BestPriceHost.com
The Hosting With 3 MySQL Databases
~~~
5/7/2002 6:01:09 PM, Nick Wilson <[EMAIL PROTECTED]> wrote:
>-BEGIN PGP SIGNED MESSAGE-
>Hash: SHA1
>
>
>* and then Victoria Reznichenko declared
>> NW> I've been searching for a solution to the following:
>>
>> NW> Is there an easy way to order by date ( - MM - DD), at the moment my
>> NW> results are coming back with the *day* ordered but not the whole date
>> NW> like this:
>>
>> NW> 02 02 2002
>> NW> 03 03 2005
>> NW> 18 02 2003
>>
>> NW> See what I mean?
>>
>> What is your column type? Why you don't use ORDER BY your_date_column
>> in the SELECT statement? It should work, but I can say you exactly
>> because I don't know your table structure. The other causes of wrong
>> result might be that something is not correct in your SQL statement.
>
>Right. here is the db schema: Feel free to berate me if I'm missing the
>obvious :-)
>CREATE TABLE iaadiplom_timetable
>(
>id INT NOT NULL AUTO_INCREMENT,
>module ENUM('0', '1', '2', '3', '4', '5'),
>week VARCHAR(255) NULL,
>date DATE NOT NULL,
>ektione VARCHAR(255) NULL,
>title TEXT NULL,
>teacher_id INT NULL,
>PRIMARY KEY(id)
>)
>
>and here is my select:
>
>$qry="SELECT id, module, week, ";
>$qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, ";
>$qry.="ektione, title, teacher_id ";
>$qry.="FROM $this->table ";
>$qry.="WHERE module = '$module' ";
>$qry.="ORDER BY date ASC";
>
>
>Thanks very much for the help!
>- --
>Nick Wilson // www.explodingnet.com
>
>
>
>-BEGIN PGP SIGNATURE-
>Version: GnuPG v1.0.6 (GNU/Linux)
>
>iD8DBQE819AVHpvrrTa6L5oRAtj2AJ9CFMVEZEyBZKxy7zhOoSmMDAWUrwCfelhu
>UbSqDCWAxbYSgmGA2/u6OL8=
>=qD2+
>-END PGP SIGNATURE-
>
>-
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
Tahir Ata Barry
CEO, Sulata iSoft
http://www.sulata.com.pk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
* and then Victoria Reznichenko declared
> NW> I've been searching for a solution to the following:
>
> NW> Is there an easy way to order by date ( - MM - DD), at the moment my
> NW> results are coming back with the *day* ordered but not the whole date
> NW> like this:
>
> NW> 02 02 2002
> NW> 03 03 2005
> NW> 18 02 2003
>
> NW> See what I mean?
>
> What is your column type? Why you don't use ORDER BY your_date_column
> in the SELECT statement? It should work, but I can say you exactly
> because I don't know your table structure. The other causes of wrong
> result might be that something is not correct in your SQL statement.
Right. here is the db schema: Feel free to berate me if I'm missing the
obvious :-)
CREATE TABLE iaadiplom_timetable
(
id INT NOT NULL AUTO_INCREMENT,
module ENUM('0', '1', '2', '3', '4', '5'),
week VARCHAR(255) NULL,
date DATE NOT NULL,
ektione VARCHAR(255) NULL,
title TEXT NULL,
teacher_id INT NULL,
PRIMARY KEY(id)
)
and here is my select:
$qry="SELECT id, module, week, ";
$qry.="DATE_FORMAT(date, \"%d %m %Y\") as date, ";
$qry.="ektione, title, teacher_id ";
$qry.="FROM $this->table ";
$qry.="WHERE module = '$module' ";
$qry.="ORDER BY date ASC";
Thanks very much for the help!
- --
Nick Wilson // www.explodingnet.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.6 (GNU/Linux)
iD8DBQE819AVHpvrrTa6L5oRAtj2AJ9CFMVEZEyBZKxy7zhOoSmMDAWUrwCfelhu
UbSqDCWAxbYSgmGA2/u6OL8=
=qD2+
-END PGP SIGNATURE-
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by date
Nick, Tuesday, May 07, 2002, 12:06:31 PM, you wrote: NW> I've been searching for a solution to the following: NW> Is there an easy way to order by date ( - MM - DD), at the moment my NW> results are coming back with the *day* ordered but not the whole date NW> like this: NW> 02 02 2002 NW> 03 03 2005 NW> 18 02 2003 NW> See what I mean? What is your column type? Why you don't use ORDER BY your_date_column in the SELECT statement? It should work, but I can say you exactly because I don't know your table structure. The other causes of wrong result might be that something is not correct in your SQL statement. NW> I can't beleive there is nothing that can be done with sql about this NW> but I can't seem to find anything that will tell me how to do it so a little pointer would be smashing. NW> Many thanks... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Order by date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all I've been searching for a solution to the following: Is there an easy way to order by date ( - MM - DD), at the moment my results are coming back with the *day* ordered but not the whole date like this: 02 02 2002 03 03 2005 18 02 2003 See what I mean? I can't beleive there is nothing that can be done with sql about this but I can't seem to find anything that will tell me how to do it so a little pointer would be smashing. Many thanks... - -- Nick Wilson // www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE815hbHpvrrTa6L5oRAtdGAJ9MhgAcbdHqQnBJpfRPHGCf3msHvQCeLq0S qL5vUD6SumKXFFL/mOnhp/0= =k44P -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date error!!!
Hi, Wakan wrote : > > Hi, > I've noticed this problem, even if I don't know if it's really a mysql problem: > if I store a date in standard format, in a date field, all kinds of > ordering are OK. > But if I change the output format with: DATE_FORMAT(data,'%d-%m-%Y'), > the order by clause attempt to order the new format incorrectly, because > I've an output like this: 12-2-2002, 12-3-2002, 12-4-2002, and 13-2-2002!!! > What can you say about this? Nothing until you show us your query. I suspect that you are ordering by formatted date, which is a string, instead of date; but you don't give enough information to confirm that ! > Carlo > Regards -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: order by date error!!!
Wakan <[EMAIL PROTECTED]> wrote: > But if I change the output format with: DATE_FORMAT(data,'%d-%m-%Y'), > the order by clause attempt to order the new format incorrectly, because > I've an output like this: 12-2-2002, 12-3-2002, 12-4-2002, and 13-2-2002!!! > What can you say about this? Can you give an example of a query that has the problem? It sounds like you're using DATE_FORMAT() in the ORDER BY as well as in the SELECT. If you do SELECT DATE_FORMAT(data,'%d-%m-%Y') ORDER BY data, and data is a DATE or DATETIME, then you shouldn't have that problem. -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: order by date error!!!
> But if I change the output format with: DATE_FORMAT(data,'%d-%m-%Y'), > the order by clause attempt to order the new format > incorrectly, because > I've an output like this: 12-2-2002, 12-3-2002, 12-4-2002, > and 13-2-2002!!! I normally do this: SELECT DATE_FORMAT(datefield,'%d-%m-%Y') as datefield FROM table ORDER BY table.datefield This forces mysql to order by the data in the table instead of the result 'datefield' (note the same name of both the field and the result). :wq //andreas http://phpwizard.dk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
order by date error!!!
Hi, I've noticed this problem, even if I don't know if it's really a mysql problem: if I store a date in standard format, in a date field, all kinds of ordering are OK. But if I change the output format with: DATE_FORMAT(data,'%d-%m-%Y'), the order by clause attempt to order the new format incorrectly, because I've an output like this: 12-2-2002, 12-3-2002, 12-4-2002, and 13-2-2002!!! What can you say about this? Carlo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
