CODEWITHSUNDEEP
sqlsql-order-byfirebird
Britto
Britto

Reputation: 515

Complex ORDER BY Statement

I have this query:

SELECT itemID, item, note, sourceItemID, title, collectionName,
       count(tagID) as qtTag,
       count(modalityID) as qt_Modality, extra
       FROM items INNER JOIN factors ON items.itemID = factors.zotero_itemID
       WHERE (collectionID = :collectionID) AND (tagID = :tagID1 OR tagID = :tagID2) group by itemID, item, note, sourceItemID, title, collectionName, extra ORDER BY EXTRA;

Now, every ITEM is connected to one or many TAGID's. What I need is to order the result in a way that ITEMS linked to TagID1 and TagID2 comes first in order the others could be ordered by EXTRA field. Is it possible to do that? I have tried with ORDER BY CASE statement but I cannot make it work it out.

this is a sample of the result:

enter image description here

As you can see, the items are ordered by the EXTRA field. But I need to put ITEMS linked to TagID1 and TagID2 first, but these fields are not shown in the result. All I need is the order.

I had tried this code, but doesn't work:

SELECT itemID, item, note, sourceItemID, title, collectionName,
       count(tagID) as qtTag,
       count(modalityID) as qt_Modality, extra
       FROM items INNER JOIN factors ON items.itemID = factors.zotero_itemID
       WHERE (collectionID = :collectionID) AND (tagID = :tagID1 OR tagID = :tagID2) group by itemID, item, note, sourceItemID, title, collectionName, extra ORDER BY CASE WHEN tagID1 = :TagID1 THEN 1 WHEN tagID2 = :tagID2 THEN 2 END,  EXTRA;

Obs. I'm using Firebird 3.0 database

Upvotes: 0

Views: 112

Answers (1)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Try like this;

SELECT itemID, item, note, sourceItemID, title, collectionName,
       count(tagID) as qtTag,
       count(modalityID) as qt_Modality, 
       extra
       FROM items INNER JOIN factors ON items.itemID = factors.zotero_itemID
       WHERE (collectionID = :collectionID) AND (tagID = :tagID1 OR tagID = :tagID2) 
       group by itemID, item, note, sourceItemID, title, collectionName, extra, tagID
       ORDER BY        
       (CASE
       WHEN tagID = 'tagID1' THEN 1
       WHEN tagID = 'tagID2' THEN 2
       ELSE 3
       END), extra

Upvotes: 1

Related Questions