How to select rows from only the first N rows in a table?
It seems that there is no direct support to limit 'select' to only the first N rows in a table. Could you let me know what the best way select rows from the first N rows in a table is? -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select rows from only the first N rows in a table?
It seems that there is no direct support to limit 'select' to only the first N rows in a table. Could you let me know what the best way select rows from the first N rows in a table is? LIMIT usually works fine ;-) http://dev.mysql.com/doc/refman/5.0/en/select.html With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join syntax problem
As Tom Worster said, print($query); would show you what the query was trying to run. Without testing it, you also have some other whitespace issues between the hw.wildlife and FROM, and also, i m unsure of the asterix infront of the *images. On another note, when I do my JOINs, I tend to write ON (table1.field=jointable.field) rather than just ON (field). And on a final thought, the where cause, seems to be the join clause as well, so isn't that redundant? (or is would that only be in the way that i said i do my joins?) ++ | Steven Staples | ++ | I may be wrong, but at least I tried...| ++ -Original Message- From: Gary [mailto:g...@paulgdesigns.com] Sent: April 26, 2010 10:29 PM To: mysql@lists.mysql.com Subject: Re: Join syntax problem Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date: 04/26/10 02:31:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query Help!
Hi All, I have a query I need to run but can't think how to get this working so I am hoping someone can advise. I have a table which logs start and end times of Scheduled jobs. It includes for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both `StartDateTime` and `EndDateTime` are 'datetime' datatypes. What I need to do is find all times in a day when there was nothing running on the system - so all times which do not occur between any of the `StartDateTime` and `EndDateTime` values for a particular day. A simple example, if the table had values *DayId StartDateTimeEndDateTime 12010-02-26 16:40:27 2010-02-26 16:41:27 12010-02-26 16:41:21 2010-02-26 16:45:57 12010-02-26 16:47:01 2010-02-26 16:49:21 12010-02-26 16:49:27 2010-02-26 16:49:55 *I can see the system was free between 16:45:57 and 16:47:01 on 26th February 2010 and this is what I would need the query to return only working with a lot more data. Any ideas? Thank you in advance for any help, suggestions. This is currently on a MySQL 5.1 system. Regards -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Query Help!
Hmm. You seem to have overlap, too. I suspect this would be easiest to do in code - the data you're looking for doesn't exist in the data you have, only the opposite of that data does. You could try populating a table with a full day, using the resolution you need (1 minute resolution means 1440 records) and then (somehow) join with your data table and use not between; but they you'd get a list of free $resolution blocks, which you may still want to aggregate into from-to blocks using code. On Tue, Apr 27, 2010 at 3:47 PM, John Daisley mg_s...@hotmail.com wrote: Hi All, I have a query I need to run but can't think how to get this working so I am hoping someone can advise. I have a table which logs start and end times of Scheduled jobs. It includes for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both `StartDateTime` and `EndDateTime` are 'datetime' datatypes. What I need to do is find all times in a day when there was nothing running on the system - so all times which do not occur between any of the `StartDateTime` and `EndDateTime` values for a particular day. A simple example, if the table had values *DayId StartDateTimeEndDateTime 12010-02-26 16:40:27 2010-02-26 16:41:27 12010-02-26 16:41:21 2010-02-26 16:45:57 12010-02-26 16:47:01 2010-02-26 16:49:21 12010-02-26 16:49:27 2010-02-26 16:49:55 *I can see the system was free between 16:45:57 and 16:47:01 on 26th February 2010 and this is what I would need the query to return only working with a lot more data. Any ideas? Thank you in advance for any help, suggestions. This is currently on a MySQL 5.1 system. Regards -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Query Help!
Not tested, but I think it can help you or at least give you an ideia on how to do it. select EndDateTime + INTERVAL 1 SECOND as startLazy, (select StartDateTime - INTERVAL 1 SECOND from table t2 where t2.StartDateTime t1.EndDateTime limit 1) as endLazy from table t1 where (select StartDateTime - INTERVAL 1 SECOND from table t2 where t2.StartDateTime = t1.EndDateTime limit 1) (EndDateTime + INTERVAL 1 SECOND) John Daisley mg_s...@hotmail.com escreveu na mensagem news:m2x571a6edf1004270647j3d1ef220n4eb9394c339f1...@mail.gmail.com... Hi All, I have a query I need to run but can't think how to get this working so I am hoping someone can advise. I have a table which logs start and end times of Scheduled jobs. It includes for simplicity a `DayID`, `StartDateTime` and `EndDateTime` column. Both `StartDateTime` and `EndDateTime` are 'datetime' datatypes. What I need to do is find all times in a day when there was nothing running on the system - so all times which do not occur between any of the `StartDateTime` and `EndDateTime` values for a particular day. A simple example, if the table had values *DayId StartDateTimeEndDateTime 12010-02-26 16:40:27 2010-02-26 16:41:27 12010-02-26 16:41:21 2010-02-26 16:45:57 12010-02-26 16:47:01 2010-02-26 16:49:21 12010-02-26 16:49:27 2010-02-26 16:49:55 *I can see the system was free between 16:45:57 and 16:47:01 on 26th February 2010 and this is what I would need the query to return only working with a lot more data. Any ideas? Thank you in advance for any help, suggestions. This is currently on a MySQL 5.1 system. Regards -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select rows from only the first N rows in a table?
In the last episode (Apr 27), Martijn Tonies said: It seems that there is no direct support to limit 'select' to only the first N rows in a table. Could you let me know what the best way select rows from the first N rows in a table is? LIMIT usually works fine ;-) http://dev.mysql.com/doc/refman/5.0/en/select.html That may noy be what Peng is looking for, though. LIMIT filters the output resultset, not the input table. Since in the logical SQL world, tables are unsorted collections of rows, it doesn't make sense to limit on them directly. You can do this, however: SELECT * FROM ( SELECT * FROM products ORDER BY DATE LIMIT 10 ) AS t WHERE color='red' This will fetch the 10 oldest products in the table and then return only the red ones. Compare to SELECT * FROM products WHERE color='red' ORDER BY DATE LIMIT 10 which will return the 10 oldest red products, even if they are the 10 newest records in the table. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to select rows from only the first N rows in a table?
In the last episode (Apr 27), Martijn Tonies said: It seems that there is no direct support to limit 'select' to only the first N rows in a table. Could you let me know what the best way select rows from the first N rows in a table is? LIMIT usually works fine ;-) http://dev.mysql.com/doc/refman/5.0/en/select.html That may noy be what Peng is looking for, though. LIMIT filters the output resultset, not the input table. Since in the logical SQL world, tables are unsorted collections of rows, it doesn't make sense to limit on them directly. You can do this, however: SELECT * FROM ( SELECT * FROM products ORDER BY DATE LIMIT 10 ) AS t WHERE color='red' This will fetch the 10 oldest products in the table and then return only the red ones. Compare to SELECT * FROM products WHERE color='red' ORDER BY DATE LIMIT 10 which will return the 10 oldest red products, even if they are the 10 newest records in the table. I fully agree on the differences, and yes, you're very right about unsorted collections of rows. Basically, you're selecting from an intermediate limited resultset set here via a derived table. Not sure what the original posted wanted though. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
order by numeric value
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
slow query on replication master and slave
Hi, Is it true that the performance of running a query on a live replication master and slave has to be much slower than running a query on a static server? I've tried to run the following query on a replication master and it takes 1 min 13.76 sec to finish. SELECT *, ABS(timeA-1266143632) as distance FROM tableA WHERE timeA - 1266143632 = 0 ORDER BY distance LIMIT 1; And if I run it on the replication slave, it takes 24.15 sec. But if I dump the whole database to another machine as static, it only takes 3.70 sec or even less to finish. The table has 386 columns and timeA is an index. Is there a way to improve the query or any other factors that would affect the performance? Thanks. Kandy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
Keith Clark skrev:
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
Balance is the result of a string operation (concat), and abs is a
numeric function that won't work on strings.
You should add a field to the result with the numerical value of
Balance, and then sort on that.
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 2010-04-28 at 00:18 +0200, Carsten Pedersen wrote:
Keith Clark skrev:
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
Balance is the result of a string operation (concat), and abs is a
numeric function that won't work on strings.
You should add a field to the result with the numerical value of
Balance, and then sort on that.
/ Carsten
You cannot CONVERT at the order by statement?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: Keith Clark keithcl...@k-wbookworm.com
To: mysql@lists.mysql.com
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net
smime.p7s
Description: S/MIME cryptographic signature
RE: order by numeric value
That won't work on a value like $1.00
select CAST('$1.00' as decimal(8,2));
+---+
| CAST('$1.00' as decimal(8,2)) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1292 | Truncated incorrect DECIMAL value: '$1.00' |
+-+--++
1 row in set (0.00 sec)
It would have to be something like:
select CAST(REPLACE('$1.00','$','') as decimal(8,2)) ;
+---+
| CAST(REPLACE('$1.00','$','') as decimal(8,2)) |
+---+
| 1.00 |
+---+
1 row in set (0.00 sec)
Which in that case, it's better to just select balance without the dollar sign
and order on that column.
Regards,
Gavin Towey
-Original Message-
From: DaWiz [mailto:da...@dawiz.net]
Sent: Tuesday, April 27, 2010 3:46 PM
To: Keith Clark; mysql@lists.mysql.com
Subject: Re: order by numeric value
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: Keith Clark keithcl...@k-wbookworm.com
To: mysql@lists.mysql.com
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
RE: order by numeric value
But I'd prefer not to see the extra sorting field.
Keith
On Tue, 2010-04-27 at 15:49 -0700, Gavin Towey wrote:
That won't work on a value like $1.00
select CAST('$1.00' as decimal(8,2));
+---+
| CAST('$1.00' as decimal(8,2)) |
+---+
| 0.00 |
+---+
1 row in set, 1 warning (0.00 sec)
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1292 | Truncated incorrect DECIMAL value: '$1.00' |
+-+--++
1 row in set (0.00 sec)
It would have to be something like:
select CAST(REPLACE('$1.00','$','') as decimal(8,2)) ;
+---+
| CAST(REPLACE('$1.00','$','') as decimal(8,2)) |
+---+
| 1.00 |
+---+
1 row in set (0.00 sec)
Which in that case, it's better to just select balance without the dollar
sign and order on that column.
Regards,
Gavin Towey
-Original Message-
From: DaWiz [mailto:da...@dawiz.net]
Sent: Tuesday, April 27, 2010 3:46 PM
To: Keith Clark; mysql@lists.mysql.com
Subject: Re: order by numeric value
Try
order by CAST(Balance as decimal(8,2)) asc;
Cast will work in the order by.
Glenn Vaughn
- Original Message -
From: Keith Clark keithcl...@k-wbookworm.com
To: mysql@lists.mysql.com
Sent: Tuesday, April 27, 2010 3:52 PM
Subject: order by numeric value
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in
the contents of this message, which arise as a result of e-mail transmission.
[FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA,
FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by
coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
asc;
should do the trick.
Jesper
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 2010-04-28 at 08:57 +1000, Jesper Wisborg Krogh wrote:
On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by
coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
asc;
should do the trick.
Jesper
Perfectamundo! I thought there would have been a more elegant way but
this works just fine. Thanks.
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sql to duplicate records with modified value
thanks, Ray, that worked well (btw, you have a typo, 'Independant' instead of 'Independent') btw2, I have a pdf with some 15,000 names that I would like to display with a search function, I email you later, maybe you can help me with that -- Voytek quote who=Ray Cauchi Hi Voytek You could try some variation of: INSERT INTO inserttable (user, maildir) SELECT REPLACE(user, '@', 'spam@') as user, CONCAT(maildir,'.spam/') as maildir FROM selecttable [WHERE ..] the where bit is optional of course! let me know how you go - hope you are keeping well! ray At 03:17 PM 27/03/2010, Voytek Eymont wrote: I have Postfix virtual mailboxes in MySQL table like below: I'd like to duplicate all records whilst MODIFYING two fields like so: current record has format like: user 'usern...@domain.tld' maildir 'domain.tld/usern...@domain.tld/' add new record that has: user 'username+s...@domain.tld' maildir 'domain.tld/usern...@domain.tld/.spam/' so that I'll end up with two record, existing, plus new one field 'user' - insert '+spam' ahead of '@' field 'maildir' append '.spam/' what's the best way mysql Ver 14.7 Distrib 4.1.22, for pc-linux-gnu (i686) using readline 4.3 Server version: 4.1.22-standard mysql show tables; +---+ | Tables_in_postfix | +---+ | admin | | alias | | config| | domain| | domain_admins | | fetchmail | | log | | mailbox | | vacation | | vacation_notification | +---+ 10 rows in set (0.00 sec) -- Voytek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@tweek.com.au ( T W E E K ! ) PO Box 15 Wentworth Falls NSW Australia 2782 | p:+61 2 4702 6377 (Sydney/Penrith/Blue Mountains) | p:+61 2 4915 8532 (Newcastle/Hunter) | f:+61 2 8456 5743 | m:0414 270 400 | e:ray at tweek dot com dot au | w:www dot tweek dot com dot au 'What is more immoral than war?' - Marquis de Sade -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
