order by the greater of 2 fields

[Anthony Smith]

Hi all,

I have table with 2 fields, withhold _until_date and esi_withhold_until.

Is is it possible to order by so the the greater of the 2 fields was at the top of the list.

cheers 

Anthony  

[黄祥]

pls try (not tested)

myorder = db.mytable.withhold_until_date | db.mytable.esi_withhold_until

db().select(db.mytable.ALL, orderby = myorder)

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer

best regards,

stifan

[Anthony]

On Monday, October 24, 2016 at 8:55:23 AM UTC-4, 黄祥 wrote:

pls try (not tested)

myorder = db.mytable.withhold_until_date | db.mytable.esi_withhold_until

db().select(db.mytable.ALL, orderby = myorder)

That will order by withhold_until_date, with esi_withhold_until breaking any ties -- it will not order by the max of those two fields.

Anthony

[Anthony]

On Monday, October 24, 2016 at 7:11:29 AM UTC-4, Anthony Smith wrote:

Hi all,

I have table with 2 fields, withhold _until_date and esi_withhold_until.

Is is it possible to order by so the the greater of the 2 fields was at the top of the list.

I think there are various ways to achieve this in SQL, though not sure you could easily use the DAL to generate that SQL. Does you query involve a limitby or pagination (i.e., does the sorting have to be done by the database as part of the query)? If not, you could instead do the sorting in Python -- check out the Rows.sort method: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#find--exclude--sort.

Anthony

[Anthony Smith]

Hi all, 

This gets the results using sql but only able to do one or the other is the controller in web2py

select * from animalTask order by GREATEST(COALESCE(withhold_until_date,0),COALESCE(esi_withhold_until_date,0)) DESC 

Anthony

[Dave S]

On Tuesday, October 25, 2016 at 3:56:42 AM UTC-7, Anthony Smith wrote:

Hi all, 

This gets the results using sql but only able to do one or the other is the controller in web2py

select * from animalTask order by GREATEST(COALESCE(withhold_until_date,0),COALESCE(esi_withhold_until_date,0)) DESC 

Anthony

Can you use the executesql() helper?

<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#executesql>

/dps
 

[Anthony]

Hi all, 

This gets the results using sql but only able to do one or the other is the controller in web2py

select * from animalTask order by GREATEST(COALESCE(withhold_until_date,0),COALESCE(esi_withhold_until_date,0)) DESC 

What do you mean by "one or the other" -- what is "the other"?

Anthony

[Anthony Smith]

I meant it would only order by 1 field not the other,but the sql works fine.

just implementing it now  

I will work on it 

cheers