On Wed, May 21, 2008 at 9:45 PM, David Perron [EMAIL PROTECTED] wrote:
Hello MySQL Users-
I am pretty sure this is a simple question and I am over thinking how to
solve the problem, so I am hoping the community can help.
I am selecting a pretty straightforward aggregation from a single stats
David,
What I am trying to limit this query to is the top 100 details ordered by
SUM(Volume) DESC for each unique LongDescription
For some solutions see 'Within-group quotas (Top N per group)' at
http://www.artfulsoftware.com/infotree/queries.php
PB
-
David Perron wrote:
Hello MySQL
Hello MySQL Users-
I am pretty sure this is a simple question and I am over thinking how to
solve the problem, so I am hoping the community can help.
I am selecting a pretty straightforward aggregation from a single stats
table with the following format:
SELECT
Description
Hello everybody,
I got a small problem with ordering on en ENUM field. The values in this
field are:
- to be started
- started
- finished
- canceled
And i want to order on this field, but in the direction the are above
here (and not alpabetically).
Is that possible?
- Mike
--
Medusa,
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
on.
To answer your question:
ORDER BY `status` = 'to be started', `status` = 'started', `status` =
'finished', `status` = 'canceled'
Mike van Hoof wrote:
Hello everybody,
I got a small problem with ordering on en ENUM field. The values in
this field are:
- to be started
- started
- finished
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 following
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
I am having a problem with using a select statement to retrieve a result set
in a particular order. 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). However, the table has multiple
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
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
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 ','');
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 |
| |
| |
| |
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 ''; ''
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:
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
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 exactly the
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 to be able
to return the word The but have it
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
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...
easy:
select subscriber, ... from view01
order by subscriber
Rhino
- Original Message -
From: Jack Lauman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, June 26, 2005 2:06 PM
Subject: ORDER by Question
I'm using a query similar to the following to get an ordered list
is
usually true. Not this time though!
Rhino
- Original Message -
From: Hassan Schroeder [EMAIL PROTECTED]
To: Jack Lauman [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Sunday, June 26, 2005 2:44 PM
Subject: Re: ORDER by Question
Jack Lauman wrote:
SELECT ORDER BY Subscriber ASC
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 to be
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=cityvalue=Blaine
Thanks again for your help.
Jack
Mathias wrote:
Selon Jack Lauman [EMAIL PROTECTED]:
I'm
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
1
i have an question about ordering a set of records...
ab c d
-
1Tax 120001.33
1Tax 115002.5
1Tax 110003.25
1Tax 10 4.5
You can try to use two subqueries and an union ala
SELECT * FROM tab where c0 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,
I have a table, let's call it item, which has a field containing a
comma separated list of IDs of related items ( same table ).
I know, this is far away from a correctly normalized structure and this
stuff should be handled using another table for the link, but I'm
working on a
* Giulio
I have a table, let's call it item, which has a field containing a
comma separated list of IDs of related items ( same table ).
I know, this is far away from a correctly normalized structure and this
stuff should be handled using another table for the link, but I'm
working on a
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
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 | 4 |
| B16032 | 6
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 |
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
' in the 'order by' is.)
Rhino
- Original Message -
From: Michael Kruckenberg [EMAIL PROTECTED]
To: Dirk Bremer (NISC) [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 9:46 AM
Subject: Re: ORDER BY Question
Dirk Bremer (NISC) wrote:
The following query produces
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 by' is the
Okay I have a field in my db called MemberLevel in this field you can be one of 5
levels.
Platinum
Gold
Silver
Paying
Non-Paying
now my question is, how would I ORDER BY MemberLevel and get it to come out in the
above order? I have been racking my brains for a week on this one and any
From: Lewis, Jason [mailto:[EMAIL PROTECTED]
Okay I have a field in my db called MemberLevel in this field
you can be one of 5 levels.
Platinum
Gold
Silver
Paying
Non-Paying
now my question is, how would I ORDER BY MemberLevel and get
it to come out in the above order? I
On Mon, 12 Jan 2004, Lewis, Jason wrote:
Okay I have a field in my db called MemberLevel in this field you can be one of 5
levels.
Platinum
Gold
Silver
Paying
Non-Paying
now my question is, how would I ORDER BY MemberLevel and get it to come out in the
above order? I have been
Didn't know if anyone else might need this but I was given the solution.
SELECT * FROM tablename
ORDER BY MemberLevel='Platinum' DESC,
MemberLevel='Gold' DESC,
MemberLevel='Silver' DESC,
MemberLevel='Paying' DESC,
MemberLevel='Non-Paying' DESC;
Thanks again Mike!
Jason
--
MySQL General
From: Lewis, Jason [mailto:[EMAIL PROTECTED]
Didn't know if anyone else might need this but I was given
the solution.
SELECT * FROM tablename
ORDER BY MemberLevel='Platinum' DESC,
MemberLevel='Gold' DESC,
MemberLevel='Silver' DESC,
MemberLevel='Paying' DESC,
* Mike Johnson
From: Lewis, Jason [mailto:[EMAIL PROTECTED]
Didn't know if anyone else might need this but I was given
the solution.
SELECT * FROM tablename
ORDER BY MemberLevel='Platinum' DESC,
MemberLevel='Gold' DESC,
MemberLevel='Silver' DESC,
MemberLevel='Paying'
If anyone has any suggestions, they would be greatly appreciated. I've
searched though my resources and online, and perhaps my newbie frustration
is making me overlook something simple.
What I'm trying to do is sort by a column with by pre-set criteria; I've a
political database with events
Brandyn Riffle wrote:
What I'm trying to do is sort by a column with by pre-set criteria; I've
a political database with events with columns for the year, month, day,
and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...)
after sorting by year. The sorting by year part was
At 20:36 -0500 12/12/03, Brandyn Riffle wrote:
If anyone has any suggestions, they would be greatly appreciated.
I've searched though my resources and online, and perhaps my newbie
frustration is making me overlook something simple.
What I'm trying to do is sort by a column with by pre-set
Chuck Gadd wrote:
Brandyn Riffle wrote:
What I'm trying to do is sort by a column with by pre-set criteria;
I've a political database with events with columns for the year,
month, day, and event. I'd like to order by months, (e.g. JAN, FEB,
MAR, etc...) after sorting by year. The sorting by
(month,'JAN','FEB','MAR',...,'NOV','DEC')
will map month values onto the numbers 1 to 12 and sort them
numerically.
http://www.mysql.com/doc/en/String_functions.html
From: Paul DuBois [EMAIL PROTECTED]
To: Brandyn Riffle [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: customizing order
I have a query:-
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;
The problem I have is that the Order By
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
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 Transaction
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: order by question
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 [mailto
-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 gallery_id IN ( 3, 1, 2 ) ORDER BY ?
What I'm trying
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 SELECT and it works!. I have MySQL 4.0.4
* 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:
URL: http://www.mysql.com/doc/en/News-3.20.17.html
--
Roger
--
MySQL General Mailing List
For list archives:
* 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()
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!
-m
--
MySQL General Mailing List
For list
All,
I have a question about grouping numbers.
Lets say I have 10 records each containing a numeric value:-
1
2
3
5
10
-1
-2
-3
-4
-5
What I wish to do is to select the records from the database but group them
like this :-
e.g. by the highest value (ASC or DESC) regarldess of whether the value
Hi Martin,
I understand the ABS() function is used for this.
Best regards
Nils Valentin
Tokyo/Japan
2003 7 24 23:42Martin Moss :
All,
I have a question about grouping numbers.
Lets say I have 10 records each containing a numeric value:-
1
2
3
5
10
-1
-2
-3
-4
-5
What I wish
select order by abs(field) desc;
Hope this helps,
Joseph Bueno
Martin Moss wrote:
All,
I have a question about grouping numbers.
Lets say I have 10 records each containing a numeric value:-
1
2
3
5
10
-1
-2
-3
-4
-5
What I wish to do is to select the records from the database but group
Thanks to everyone who Helped,
Regards
Marty
- Original Message -
From: Joseph Bueno [EMAIL PROTECTED]
To: Martin Moss [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 3:56 PM
Subject: Re: New Group By, order by question
select order by abs(field) desc
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 owner_names.Name
Without the order by clause this is a pretty quick query, but with
-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
63 matches
Mail list logo