dd a value to the
enum list via ALTER TABLE.
-Original Message-
From: Mike van Hoof [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 21, 2007 3:19 AM
To: Christophe Gregoir
Cc: mysql
Subject: Re: ORDER BY question
Thanks, that is also a solution.
Friend of mine pointed me to the
Thanks, that is also a solution.
Friend of mine pointed me to the following:
SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f,
CASE `status`
WHEN 'not yet started' then 1
WHEN 'in progress' then 4
WHEN 'finished' then 5
Hey Mike,
Sounds like you would be better of with an ENUM of integers, e.g.
ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so on.
To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` =
'finished', `status` = 'canceled'
Mike van Hoo
On 17/08/2005, Schimmel LCpl Robert B wrote:
> If I do a select * from the table
> without an order by clause, I get the results in the order which they
> were entered into the table (which is how I want them).
This is not correct (e.g. on a MyISAM table in which you have done
deletes - see exam
Johan Höök wrote:
Hi,
the basic thing is that you must never assume anything on what
order you're getting your rows back if you're not using an order by.
This said I guess one way for you to do this is to add a row-number
column, preferbly auto-increment, and then order by that column.
/Johan
Hi,
the basic thing is that you must never assume anything on what
order you're getting your rows back if you're not using an order by.
This said I guess one way for you to do this is to add a row-number
column, preferbly auto-increment, and then order by that column.
/Johan
Schimmel LCpl Rober
Selon Hassan Schroeder <[EMAIL PROTECTED]>:
> Mathias wrote:
>
> > This is the right structure including "The" in the middle :
> > mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The'
> >then REPLACE(name,'The ','')
> >else name end;
>
> ? all of which produces
Mathias wrote:
This is the right structure including "The" in the middle :
mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The'
>then REPLACE(name,'The ','')
else name end;
? all of which produces exactly the same result as:
SELECT * FROM names ORDER BY T
Right,
i have all my attention on the " The Yeti" order, and didn't see the rest.
This is the right structure including "The" in the middle :
mysql> SELECT * FROM names ORDER BY case when substring(name,1,3)='The' then
REPLACE(name,'The ','')
-> else name end;
++
| name
Mathias wrote:
you didn't give an alternative, but i've forgotten just a '^' :
mysql> SELECT * FROM names ORDER BY REPLACE(name,'^The ','');
No, sorry -- that doesn't work at all; REPLACE takes a string,
not a regex. Look at your example below: 'The ' should be
after ''; '' shou
Hi,
you didn't give an alternative, but i've forgotten just a '^' :
mysql> SELECT * FROM names ORDER BY REPLACE(name,'The ','');
++
| name |
++
| |
| The |
| The |
| |
| |
| |
Hi
> this,among other answers, can be done :
>
> mysql> select * from names;
> +--+
> | name |
> +--+
> | |
> | The |
> | |
> | The |
> | |
> +--+
> 5 rows in set (0.02 sec)
>
> mysql> select * from names order by replace(name,'The
My thanks to all that responded. I used Mathias's suggestion to solve
the problem. You can see the results here.
http://www.tasteofwhatcom.com/restaurants-tow/filter.jsp?field=city&value=Blaine
Thanks again for your help.
Jack
Mathias wrote:
Selon Jack Lauman <[EMAIL PROTECTED]>:
I'm u
Selon Jack Lauman <[EMAIL PROTECTED]>:
> I'm using a query similar to the following to get an ordered list.
>
> SELECT ORDER BY Subscriber ASC, Name ASC;
>
> How do I change this so that if the 'Name' field begins with "The " that
> the sort begins on the second word? In other words I'd like
ose of DB2, which is
usually true. Not this time though!
Rhino
- Original Message -
From: "Hassan Schroeder" <[EMAIL PROTECTED]>
To: "Jack Lauman" <[EMAIL PROTECTED]>;
Sent: Sunday, June 26, 2005 2:44 PM
Subject: Re: ORDER by Question
> Jack Lauman wr
Is "The" your only problem word? What about "A" or "An" and other words that
are usually ignored when sorting things like book titles?
I'd be surprised if there was any way to ignore specific words in an ORDER
BY; I've been writing SQL for 20+ years and have never seen anything like
that.
I think
Jack Lauman wrote:
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change this so that if the 'Name' field begins with "The " that
the sort begins on the second word? In other words I'd like to be able
to return the word "The" but have it sort on whatever the second word is.
SELECT.
You can use:
SELECT ..
order by case substring(Name,1,4) when 'The ' then
substring(Name,5,800) else Name end
Un saludo
Juan Pedro
Jack Lauman wrote:
I'm using a query similar to the following to get an ordered list.
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change this
You can use:
SELECT ..
order by case substring(Name,1,4) when 'The ' then
substring(Name,5,800) else Name end
Un saludo
Juan Pedro
Jack Lauman wrote:
I'm using a query similar to the following to get an ordered list.
SELECT ORDER BY Subscriber ASC, Name ASC;
How do I change this
In article <[EMAIL PROTECTED]>,
dan orlic <[EMAIL PROTECTED]> writes:
> i have an question about ordering a set of records...
> ab c d
> -
> 1Tax 120001.33
> 1Tax 115002.5
You can try to use two "subqueries" and an union ala
SELECT * FROM tab where c>0 ORDER by C UNION ALL SELECT * FROM tab where
c=0;
Hagen
dan orlic wrote:
i have an question about ordering a set of records...
ab c d
-
1
Hi Dirk,
from the excellent on-line manual:
http://dev.mysql.com/doc/mysql/en/SELECT.html
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses
using column names, column aliases, or column positions. Column positions are
integers and begin with 1:
mysql> SELECT colleg
> > select job_coop as 'Job/Coop', count(*) as Count from queue group by
> > job_coop order by Count;
>
>
> The other alternative is to omit the 'as Count' and use this query:
>
> select job_coop as 'Job/Coop', count(*)
> from queue
> group by job_coop
> order by 2;
>
> where the '2' in the 'order
ss clear too. (It won't be
apparent to some people what the effect of the '2' in the 'order by' is.)
Rhino
- Original Message -
From: "Michael Kruckenberg" <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAI
Hi Dirk,
you should be able to just add on "ORDER BY Count" after your groub clause.
/Johan
Dirk Bremer (NISC) wrote:
The following query produces the following results:
select job_coop as 'Job/Coop', count(*) as Count from queue group by
job_coop;
-- snip
Is there a way to use the ORDER BY clau
Dirk Bremer (NISC) wrote:
The following query produces the following results:
select job_coop as 'Job/Coop', count(*) as Count from queue group by
job_coop;
+--+---+
| Job/Coop | Count |
+--+---+
| B03013 |19 |
| B05044 | 9 |
| B07037 | 6 |
| B15037 |
sorry, I had some extraneous quotes in my perl code:-) zzz
- Original Message -
From: "Martin Moss" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, September 15, 2003 11:17 PM
Subject: Order By question
> I have a query:-
> SELECT recTran.TransactionID,tr.* FROM Transa
"Martin Moss" <[EMAIL PROTECTED]> wrote:
> SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN
> ReconciledTransactions recTran ON recTran.TransactionID =
> tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING
> recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC;
You are
I execute the SELECT and it works!. I have MySQL 4.0.4
Regards,
Ulises
-Mensaje original-
De: motorpsychkill [mailto:[EMAIL PROTECTED]
Enviado el: Jueves 7 de Agosto de 2003 04:43 PM
Para: mysql
Asunto: RE: order by question
> -Original Message-
> From: Roger Baklund [
> -Original Message-
> From: Roger Baklund [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 07, 2003 10:54 AM
> To: mysql
> Cc: motorpsychkill
> Subject: Re: order by question
>
>
> * motorpsychkill
> > SELECT * FROM kf_gallery WHERE gal
I think I figured it out. I had the gallery_id field zero filled... It
works now, thank you very much!
-m
> -Original Message-
> From: Roger Baklund [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 07, 2003 3:49 PM
> To: mysql
> Cc: motorpsychkill
> Subject: Re:
* motorpsychkill
> SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ?
>
> What I'm trying to do is get the results in the order specified
> in the "IN"
> clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching
> the mail archives). Thanks!
Use the FIELD()
* motorpsychkill
> Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways!
It should work also in version 3.23.54... actually, it should work since
version 3.20.17:
http://www.mysql.com/doc/en/News-3.20.17.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.m
Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways!
> -Original Message-
> From: Cabanillas Dulanto, Ulises [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 07, 2003 2:52 PM
> To: mysql
> Subject: RE: order by question
>
>
> I execute the SELEC
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
[EMAIL PROTECTED] wrote:
| I am performing a query along the lines of the following:
|
| SELECT DISTINCT property.Internal_ID
| FROM property, owner_names
| WHERE property.Internal_ID = owner_names.Internal_ID
| AND [ other conditions ]
| ORDER BY own
35 matches
Mail list logo