开发者

order by first occurence of returned results

I have two tables and want to get technology_tag from table b based on reference id from table a:

select b.dbid, b.technology_tag 
from tblConnect a, tblSites b 
where a.Site_DBID = 2 
and a.Related_Site_DBID = 1 
and (b.dbid = a.bsc_tag_dbid or b.dbid = a.related_bsc_dbid or b.dbid = a.related_msc_dbid)

What I want to have here is to order the returned rows based on first occurrence (in w开发者_如何学编程here clause)

1st- b.dbid = a.bsc_tag_dbid  
2cnd- b.dbid = a.related_bsc_dbid 
3rd- b.dbid = a.related_msc_dbid

Does anyone has a clue how to do that?


First, you should switch to using actual JOIN clauses when performing joins. That said, this ORDER BY clause should do what you want:

SELECT
    B.dbid,
    B.technology_tag
FROM tblConnect A
INNER JOIN tblSites B ON
    B.dbid IN (A.bsc_tag_dbid, A.related_bsc_dbid, A.related_msc_dbid)
WHERE
    A.Site_DBID = 2 AND
    A.Related_Site_DBID = 1
ORDER BY
    CASE
        WHEN B.dbid = A.bsc_tag_dbid THEN 1
        WHEN B.dbid = A.related_bsc_dbid THEN 2
        WHEN B.dbid = A.related_msc_dbid THEN 3
        ELSE 4   -- Not really necessary, but I always use an ELSE when I use CASE
    END
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜