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
精彩评论