# Sorting on one of two columns in database

**URL:** <https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105>\
**Category:** Rails\
**Created:** [November 15, 2006, 10:11pm UTC](https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105 "2006-11-15T22:11:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jimmyether](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e9a140/32.png) [@jimmyether](https://www.ruby-forum.com/u/jimmyether)\
**Post date:** [November 15, 2006, 10:11pm UTC](https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105/1 "2006-11-15T22:11:48Z")

</div>

Hi, all. I’ve got a list of jobs I’m trying to sort based on the  
‘location’ column. The location column is actually filled in by a  
method in the location model, and it’s “name” if the locations.name  
column is not null, otherwise it’s locations.address.

So, the long and short is that I’d like to figure out how to do this  
in my job\_controller.rb:

sort\_jobs\_by = ‘locations.name if locations.name is not null,  
otherwise use locations.address’

@jobs = Job.find(:all, :order =\> sort\_jobs\_by, :include =\> [:location])

Of course, “locations.name if locations.name is not null, otherwise  
use locations.address” is not exactly valid SQL, and so that won’t  
work. I need to know what to set sort\_jobs\_by that would accomplish  
this.

I know that I can do this:

@jobs.sort! {|a,b| a.location\_name \<=\> b.location\_name}

(where location\_name is an instance method that retrieves from the  
job.location either its name or its address).

But that’s slower, I think, and anyway doesn’t seem to be quite cricket.

Any suggestions?

---

<div class="post-metadata">

**Author:** ![jimmyether](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e9a140/32.png) [@jimmyether](https://www.ruby-forum.com/u/jimmyether)\
**Post date:** [November 15, 2006, 10:39pm UTC](https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105/2 "2006-11-15T22:39:54Z")

</div>

> (where location\_name is an instance method that retrieves from the  
> job.location either its name or its address).
> 
> But that’s slower, I think, and anyway doesn’t seem to be quite cricket.
> 
> Any suggestions?

If mysql… :order =\> “IFNULL(locations.name, locations.address)”

If postgresql, :order =\> “COALESCE(locations.name, locations.address)”

-philip

---

<div class="post-metadata">

**Author:** ![jimmyether](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e9a140/32.png) [@jimmyether](https://www.ruby-forum.com/u/jimmyether)\
**Post date:** [November 15, 2006, 10:51pm UTC](https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105/3 "2006-11-15T22:51:58Z")

</div>

Try sorting by both. Perhaps the NULL’s will show up on top, and the  
only “real” sorting will be done on the address value for those  
records.

Bart

---

<div class="post-metadata">

**Author:** ![jimmyether](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e9a140/32.png) [@jimmyether](https://www.ruby-forum.com/u/jimmyether)\
**Post date:** [November 17, 2006, 1:52am UTC](https://www.ruby-forum.com/t/sorting-on-one-of-two-columns-in-database/76105/4 "2006-11-17T01:52:42Z")

</div>

Sorry for the delay, thanks for the info! That was just what I was  
after. Sadly, I then found out that not all of the fields are NULL;  
some are just empty strings. But I’m down the road a ways with it,  
and now I’ve learned about the IFNULL(), so mucho thanks!