Order by query using specific column or alphabetical. - Coldfuison

Question

we have a coldfusion website that retrieves our categories then displays them alphabetically.

We would like to be able to force an order by manually arranging the categories with a 'sort' column with a number in but if this number is equal to 0 or null use alphabetical order.

so at the moment the query is

<cfquery name="qGetThrdCat" datasource="#request.dsn#">
    SELECT *
    FROM tbl_prdtthrdcats, tbl_scnd_thrdcat_rel
    WHERE tbl_scnd_thrdcat_rel.thrdctgry_ID = tbl_prdtthrdcats.thrdctgry_ID
    AND tbl_scnd_thrdcat_rel.scndctgry_ID = #URL.secondary#
    AND thrdctgry_archive = 0
    ORDER BY thrdctgry_Name ASC
</cfquery>

It works if I try

ORDER BY thrdctgry_Sort ASC

but I can't for the life of me join them up, mainly down to my lack of programmer skills.

Any advice would be greatly appreciated.

Solution 1

I may be misunderstanding the question, but you should be able to just sort on both columns:

ORDER BY thrdctgry_Sort ASC, thrdctgry_Name ASC

Solution 2

Here is your query with join:

select *
from tbl_prdtthrdcats p
join tbl_scnd_thrdcat_rel s
    on p.thrdctgry_ID = s.thrdctgry_ID
where
    s.scndctgry_ID = #URL.secondary# 
and thrdctgry_archive = 0

For the sorting, you can use CASE in your ORDER clause.

order by
case
    when isnull(thrdctgry_Sort, 0) = 0
    then thrdctgry_Name
    else thrdctgry_Sort
end asc

To be honest, I couldn't understand your sorting order completely, but you can play around with it more.