Order by price?
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
I think when adding the $ the number gets convert to a string. And then
the query orders it by alpha numberic.
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this
formula
to be rounded to two decimal places, and to stick a $ sign in front of
it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is
at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
--
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 price?
Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
Hint: you've made this a string comparison using CONCAT, i.e.,
alphabetical, not numerical, sorting rules apply.
How about
SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as price FROM table ORDER BY price;
Then using a printf/sprintf type formatting statement on the application/presentation layer to show to the user?
What is the application language y
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
HTH,
Robert Taylor
[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 price?
At 12:14 -0500 4/29/04, Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Sounds like you want:
ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Both of you are right Daniel, and Robert.
I've written a PHP class that tabelizes the query directly and I haven't
built in formatting options. Doing so would make the object more complicated
then I would like it to be, so I wanted to see if there is a mysql solution
to this.
I think I'll end up cheating out of this solution by simply ordering by
establishments instead of price, price will always be higher for the higher
# of establishments (I don't have any # of establishments higher than the
maximum of the quadratic equatiosn). Maybe not elegant, but it'll work :-)
Thanks guys,
Best,
Yoed
-Original Message-
From: Robert J Taylor [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 12:23 PM
To: Yoed Anis; [EMAIL PROTECTED]
Subject: Re: Order by price?
Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC
),2))
as price FROM table ORDER BY price
Hint: you've made this a string comparison using CONCAT, i.e.,
alphabetical, not numerical, sorting rules apply.
How about
SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as
price FROM table ORDER BY price;
Then using a printf/sprintf type formatting statement on the
application/presentation layer to show to the user?
What is the application language y
A note first. The column establishments is an int(10), as you see I
am taking this number through a formula, and I want the result of this
formula to be rounded to two decimal places, and to stick a $ sign in
front of it so I can easily display it on a table.
However here is the problem, in ASC order I get these results: $110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is
at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
HTH,
Robert Taylor
[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 price?
Paul DuBois wrote: *snip* Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) Perfect. (I'm saying, duh over here to myself!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Paul,
I tried doing that in one trial run, and I wasn't getting the $200 figure so
I gave up on that.
It seems like doing an order by that statement wouldn't have given me the
sort I wanted.
Don't know why that is though - in theory you should be right, I can try it
a bit more if your curious.
Best,
Yoed
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 12:28 PM
To: Yoed Anis; [EMAIL PROTECTED]
Subject: Re: Order by price?
At 12:14 -0500 4/29/04, Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC
),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I
am taking this number through a formula, and I want the result of this
formula to be rounded to two decimal places, and to stick a $ sign in
front of it so I can easily display it on a table.
However here is the problem, in ASC order I get these results: $110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110
is at very bottom)
Sounds like you want:
ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
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 price?
I want to count the passing and failing scores of a group of tests so I have a table with a row that describes each test Each test has a minimum passing score. Each test can be run an arbitrary number of times so I have a table of scores, which uses test id as a foreign key. what I would like to do is count the count of fails and passes. ideally in a single query. so the test table lookes like this -- t_id . . . . min_pass_score --- and the score table looks like this: --- score_id t_id score . . . --- I would like to select so the result set looks like this: t_id no_passes no_fails - 1 5 6 2 12 4 3192 34 . . . How can I do this? I have access to Mysql 4.0.18 although the production server still runs 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
