[Solved] Order by and multiple conditions

Discuss the database features
Post Reply
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

[Solved] Order by and multiple conditions

Post by AfTech54 »

OpenOffice > Base and I assume HSQL.

--I have one table "tbl_Filter" with one raw which contains search conditions; one column "Source" and one "AddSign"
--I also have one view "vCatalogue" with a bunch of data that I want to order by either its "Fil" or "Foto" column depending on the content in "Source" and "AddSign".
This is what i want to do
-- If "Source" = is null and "AddSign" = is not null then Order by "Foto" Asc
-- All other (3) cases Order by "Fil" ASC


SELECT * From "vCatalogue"
WHERE "Kalla" = 'RESS 1990'
Order by CASE WHEN Select "AddSign" from "tbl_Filter" is not null THEN "Foto" END ASC
this is working as long as I don't add one more condition
I´ve tried
Order by CASE WHEN Select "Source" from "tbl_Filter" is null THEN CASE WHEN Select "AddSign" from "tbl_Filter" is not null THEN "Foto" END ASC
But I can't get it working

Is it doable with multiple conditions in context to Order By? If it is, how to do it?
Last edited by AfTech54 on Sun Feb 28, 2021 9:12 pm, edited 1 time in total.
Ooo v4.1.9, Windows 10
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: Order by and multiple conditions

Post by Mountaineer »

I'd try

Code: Select all

... ORDER BY COALESCE("AddSign", "tbl_Filter", "Foto")
First and if that fails

Code: Select all

SELECT ... ,  COALESCE("AddSign", "tbl_Filter", "Foto") AS "Sort" ORDER BY "Sort"
J.
OpenOffice 3.1 on Windows Vista
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Order by and multiple conditions

Post by chrisb »

hello AfTech54,

try this:

Code: Select all

SELECT * From "vCatalogue"
WHERE "Kalla" = 'RESS 1990'
order by
	case
		when
			(select "Source" from "tbl_Filter") is null
			and
			(select "AddSign" from "tbl_Filter") is not null
			then "Foto"
		else
			"Fil"
	end
--DESC 
--if choosing to sort in descending order then insert the keyword DESC immediately after the case structure
--if sorting in ascending order (ASC) then no action is required as ASC is the default
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
AfTech54
Posts: 64
Joined: Tue Dec 31, 2013 10:08 am

Re: [Solved] Order by and multiple conditions

Post by AfTech54 »

Thanks! chrisb!

I did try with AND but I didn't use Else. Anyway it seems to be work fine now - thanks again.
Ooo v4.1.9, Windows 10
Post Reply