Reputation: 5213
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?
Upvotes: 0
Views: 70
Reputation: 1738
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
Upvotes: 1
Reputation: 3878
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
Upvotes: 0
Reputation: 171599
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
Upvotes: 0