16 Feb, 2009, Caius wrote in the 2nd comment:
Are these SQL NULL values, or empty strings?
16 Feb, 2009, Zeno wrote in the 3rd comment:
Either.
Got it though.
The data_txt and isnull is what I did.
Got it though.
Quote
$query = "SELECT DISTINCT relation.itemid, title, owner, image, background, icon, data_txt, IF(data_txt IS NULL or data_txt='',1,0) AS isnull FROM `#__sobi2_fields_data` AS fielddata, `#__sobi2_cat_items_relations` AS relation " .
"LEFT JOIN `#__sobi2_item` AS items ON relation.itemid = items.itemid " .
"WHERE ((`catid` = {$catid} {$or}) AND `published` = '1' AND (`publish_down` > '{$now}' OR `publish_down` = '{$config->nullDate}' ) AND items.itemid IN({$query}) ) AND items.itemid = fielddata.itemid AND fielddata.fieldid = 13 ORDER BY isnull ASC, {$config->listingOrdering} {$limits}";
"LEFT JOIN `#__sobi2_item` AS items ON relation.itemid = items.itemid " .
"WHERE ((`catid` = {$catid} {$or}) AND `published` = '1' AND (`publish_down` > '{$now}' OR `publish_down` = '{$config->nullDate}' ) AND items.itemid IN({$query}) ) AND items.itemid = fielddata.itemid AND fielddata.fieldid = 13 ORDER BY isnull ASC, {$config->listingOrdering} {$limits}";
The data_txt and isnull is what I did.
16 Feb, 2009, quixadhal wrote in the 4th comment:
How's this?
foo=> select * from blah order by case when description is null then 1 else 0 end, title;
title | description
——————+—————
Bobs Bakery | A bakery
Tims carshop | For car needs
Virginia Town | A great place
Jims shop |
Wonkers funhouse |
(5 rows)
16 Feb, 2009, elanthis wrote in the 5th comment:
That wouldn't handle the empty string case.
SELECT * FROM blah ORDER BY CASE WHEN description IS NULL OR description='' THEN 1 ELSE 0 END, title;
If you're using MySQL specifically (don't think this is standard SQL, could be wrong) then you can shorten it a bit:
SELECT * FROM blah ORDER BY IF(description IS NULL OR description='', 1, 0), title;
SELECT * FROM blah ORDER BY CASE WHEN description IS NULL OR description='' THEN 1 ELSE 0 END, title;
If you're using MySQL specifically (don't think this is standard SQL, could be wrong) then you can shorten it a bit:
SELECT * FROM blah ORDER BY IF(description IS NULL OR description='', 1, 0), title;
16 Feb, 2009, quixadhal wrote in the 6th comment:
Yes, I started my reply before the first reply in the thread hit, so I didn't know empty strings were lumped in with NULL's. :)
I can tell you that IF() is not valid in PostgreSQL, however your first example works as expected.
I can tell you that IF() is not valid in PostgreSQL, however your first example works as expected.
Random Picks
I want to change it so it sorts by title, and anywhere there is no desc (NULL) those items are at the bottom of the list. So the results look like this:
I know I can do this in 2 SQL select statements, but I'm looking on how to do it in one.