Another SQL statement I don't understand

Question

I know the answer to my problem has something to do with ranking, just don't know what to do exactly. The goal of my statement is to get only 5 records but give priority to the records from a certain table over another.

I have two tables: News and NewsToLocation.

Let's pretend there are 4 news items in the news table and 3 in the NewsToLocation that meet my requirements. Since I can only select 5 I want to make sure the 4 in the News table get preference and then only select 1 from the NewsToLocation table. My current query is doing the opposite and I can't figure out how to fix it.

Here is the entire query.

SELECT 
newSpotlights.* 
FROM
(SELECT 
    TOP 5 n.news_id, 
    n.is_spotlight, 
    n.location_id, 
    n.news_title, 
    CAST(n.news_content AS varchar(200)) AS news_content, 
    n.writing_date,
    n.publication_date, 
    n.end_date, 
    n.alt_uri, 
    n.youtube_code, 
    n.icon_img_file, 
    n.banner_img_file
FROM 
    (SELECT 
        n.news_id
    FROM rcde_news n
    WHERE n.location_id = 4
    AND n.is_spotlight = 1 AND n.publication_date < GETDATE() AND n.end_date > GETDATE()
    AND n.status_id = 2 /*2 = Active rcde_ItemStatus*/
    UNION
    SELECT 
        n.news_id
    FROM rcde_newsToLocation ntl
    INNER JOIN rcde_news n ON ntl.news_id = n.news_id
    WHERE ntl.location_id = 4
    AND n.status_id = 2 /*2 = Active rcde_ItemStatus*/ 
    ) AS News
INNER JOIN rcde_news n ON news.news_id = n.news_id
WHERE n.is_spotlight = 1
AND n.publication_date < GETDATE()
AND n.end_date > GETDATE()
ORDER BY publication_date DESC
) AS newSpotlights

I feel like I should be able to do some order by ranking on the most inner query but I am not sure what to do exactly. I sort of understand the ranking but no clue how I would do it on a UNION statement. Could I do something as simple as selecting 1 as rank and 2 as rank on the different parts of the union respectively and then order by rank?

Answer 1

You can just add that 1 as rank, 2 as rank to the union part of the query, and then sort by that first.

SELECT 
    TOP 5 
    n.news_id, 
    n.is_spotlight, 
    n.location_id, 
    n.news_title, 
    CAST(n.news_content AS varchar(200)) AS news_content, 
    n.writing_date,
    n.publication_date, 
    n.end_date, 
    n.alt_uri, 
    n.youtube_code, 
    n.icon_img_file, 
    n.banner_img_file
FROM 
    (SELECT 
        1 as rank,
        n.news_id
    FROM rcde_news n
    WHERE n.location_id = 4
    AND n.is_spotlight = 1 AND n.publication_date < GETDATE() AND n.end_date > GETDATE()
    AND n.status_id = 2 /*2 = Active rcde_ItemStatus*/
    UNION
    SELECT 
        2 as rank,
        n.news_id
    FROM rcde_newsToLocation ntl
    INNER JOIN rcde_news n ON ntl.news_id = n.news_id
    WHERE ntl.location_id = 4
    AND n.status_id = 2 /*2 = Active rcde_ItemStatus*/ 
    ) AS News
INNER JOIN rcde_news n ON news.news_id = n.news_id
WHERE n.is_spotlight = 1
AND n.publication_date < GETDATE()
AND n.end_date > GETDATE()
ORDER BY rank DESC, publication_date DESC

Answer 2

SELECT
    TOP 5 
    n.news_id
FROM 
    (SELECT 
        1 as ranking,
        n.news_id
    FROM rcde_news n
    WHERE blah blah
    UNION
    SELECT 
        2 as ranking,
        n.news_id
    FROM rcde_newsToLocation ntl
    WHERE blah blah

    ) AS News
ORDER BY publication_date DESC, Ranking

Answer 3

Yes, you basically have it with your rank idea:

select top 5 Column1, Column2
from (
    select top 5 Column1, Column2, 1 as Rank
    from News
    order by SomeColumn

    union all

    select top 5 Column1, Column2, 2 as Rank
    from NewsLocation
    order by SomeColumn
    ) x
order by Rank