Ordering posts by Custom Field

I have a similar issue. It seems all custom field values are strings. If there were some way to cast or convert the custom fields value as a number (float in my case, int in yours) the the order by would work.
I am trying to order by coin denominations. Values are:
.01
.05
.10
.25
.50
1.00
2.00
2.50
3.00
5.00
10.00
20.00

Order by below sorts as follows (similar to your dates):
.01
.05
.10
.25
.50
1.00
10.00
2.00
2.50
20.00
3.00
5.00

Obviously, the 10.00 and 20.00 are out of order. Is there a MySQL/SQL/WP guru who can help with this?

SELECT * FROM $wpdb->posts
	LEFT JOIN $wpdb->postmeta AS coindenom ON(
	$wpdb->posts.ID = coindenom.post_id
	AND coindenom.meta_key = 'coin-denom'
	)
	LEFT JOIN $wpdb->postmeta AS coindate ON(
	$wpdb->posts.ID = coindate.post_id
	AND coindate.meta_key = 'coin-date'
	)
	LEFT JOIN $wpdb->postmeta AS coinprice ON(
	$wpdb->posts.ID = coinprice.post_id
	AND coinprice.meta_key = 'coin-price'
	)
	LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
	WHERE $wpdb->term_taxonomy.term_id = 4
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->posts.post_status = 'publish'
	ORDER BY coindenom.meta_value, coindate.meta_value, coinprice.meta_value ASC

Calling all Guru’s. 🙂

Like those above, I’m trying to order by a numerical value (a price). I followed Roger’s advice and used ORDER BY ABS(0 + $wpdb->postmeta.meta_key) ASC

It seemed to work great, but every now and then a single entry will get ordered out of place. I can’t seem to figure out why, but with each of my lists, a single entry doesn’t seem to want to be placed in order.

Not sure what to do about it…

Thanks!

you all need this

*CAST*

i.e if ‘price’ is the name of the custom field

SELECT *,CAST($wpdb->postmeta.meta_value as DECIMAL) as price FROM $wpdb->posts

This post has been really useful to me in putting together a much more complicated search which takes on multiple query variables.

// Search build

					// Put form input into values
				  $venue_search_value = $_REQUEST['venue'] ;
				  $date_search_value = $_REQUEST['date'] ;
				  $keyword_search_value = $_REQUEST['keywords'] ;
				  $order_results_by_value = $_REQUEST['orderby'] ;

				// If not a search do nothing
				if ( $venue_search_value == '' && $date_search_value == '' && $keyword_search_value == '' ) {
					} else {

					// Start building query string

		if($venue_search_value == '') {
			$venue_search_query = '' ;
		} else {
			$venue_search_query = "AND venue.meta_value = '$venue_search_value'" ;
		}

		if($date_search_value == '') {
			$date_search_query = '' ;
		} else {
			$date_search_query = "AND starttime.meta_value LIKE '$date_search_value%' " ;
		}

// todo: find a way to search post title and post meta by keyword.
		if($keyword_search_value == '') {
			$keyword_search_query = '' ;
		} else {
			$keyword_search_query = "AND $wpdb->posts.post_content  LIKE '%$keyword_search_value%'
				OR $wpdb->posts.post_title LIKE '%$keyword_search_value%'
				OR $wpdb->postmeta.meta_value LIKE '%$keyword_search_value%' " ;
			" ;
		}

		if ($order_results_by_value == 'date') {
			$order_search_query = "starttime.meta_value" ;
		} elseif ($order_results_by_value == 'title') {
			$order_search_query = "$wpdb->posts.post_title" ;
		} else {
			$order_search_query = "starttime.meta_value" ;
		}

						// Query the database for the posts
						$query_string = "
						SELECT *
						FROM $wpdb->posts
						LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
						LEFT JOIN $wpdb->postmeta AS starttime ON ($wpdb->posts.ID = starttime.post_id AND starttime.meta_key = 'Start time')
						LEFT JOIN $wpdb->postmeta AS venue ON ($wpdb->posts.ID = venue.post_id AND venue.meta_key = 'Venue')
						WHERE $wpdb->posts.post_parent = $post->ID
						$venue_search_query
						$date_search_query
						$keyword_search_query
						AND $wpdb->posts.post_status = 'publish'
						AND $wpdb->posts.post_type = 'page'
						ORDER BY $order_search_query ASC
						";

You’ll have to forgive my awful PHP markup – I’m pretty new to this!

My main problem is with the $keyword_search_query variable. I need to search the Post Title, Post Content and all the Custom Fields Values for the user inputted $keyword_search_value .

However using the AND argument means all these SQL columns need to contain this argument (I only need one of them to contain it for SQL to return it as a result). Whereas using the OR argument in it’s place means all the other AND arguments previously given are all ignored.

Any help would be very much appreciated

You all could really use this, since no one answered you and the answer is too complex for me to explain, this article tells you how to modify the base query:

http://www.think-press.com/tips/order-posts-by-custom-key-revisited