OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] ORDER BY with conditions

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
hwoehrle
General User
General User


Joined: 21 Sep 2009
Posts: 14
Location: Germany, Ulm

PostPosted: Thu May 20, 2010 1:34 am    Post subject: [SOLVED] ORDER BY with conditions Reply with quote

Hi,

is there a possibility to order a query by conditions? For example something like::

ORDER BY field1 ASC IF field2<100
ELSE
ORDER BY field1 DESC


Last edited by hwoehrle on Fri May 21, 2010 5:47 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 20, 2010 7:10 am    Post subject: Reply with quote

hwoehrle:

You asked:

hwoehrle wrote:
is there a possibility to order a query by conditions?

Yes.

Code:

Order By CASEWHEN("field2" < 100, "field1", 100) Asc,
         "field1" Desc


Explanation: The CASEWHEN is like a spreadsheet IF statement. Therefore, it will first test for the condition of "field2" < 100 . . . if so, return "field1". Otherwise, it will return 100 ( I used 100, since, the condition was 'checking < 100' . . . could have also used any other LARGE number . . . for example . . . 9999999). Since, will have numerous rows with a 'tie' ( same value ) . . . 100 . . . will use the second condition ( in descending order ). Smile

I hope this helps, please be sure to let me / us know.

Sliderule

/Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
hwoehrle
General User
General User


Joined: 21 Sep 2009
Posts: 14
Location: Germany, Ulm

PostPosted: Fri May 21, 2010 5:47 am    Post subject: Reply with quote

sliderule you really own the OOo philosopher's stone!

Thanks again, works great
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group