Reorder Results after Select within a Stored Procedure

I have a stored procedure that's doing a search and ranking the results using freetextable. This allows me to get the top X number most relevant results if I sort by the rank column. Works great.

Here's the problem, once I have those most relevant results, I then want to sort them by a date field.  My end goal is to show the most relevant 50 results but show the most recent of those results first.

I tried nesting the whole thing in another select statement but I couldn't get that to work.

Any ideas?
ALTER PROCEDURE [dbo].[getMatchingArticles]
	@region varchar(50),
	@terms varchar(100)
AS
BEGIN
	SELECT TOP 80 article_ID, f.WeightedRank, article_title, article_tags, article_description, 
	article_link, article_image, source_ID, source_title, source_url, article_posted, article_image
	FROM table_articles INNER JOIN
	(
		SELECT [KEY], SUM(Rank) AS WeightedRank
		FROM
		(
			SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_title, @terms)
			UNION
			select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_tags, @terms)
			UNION
			select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_description, @terms)
		) as x		
		GROUP BY [KEY]
	) as f
	ON article_ID = f.[KEY]
	JOIN table_sources 
	ON article_listing = source_ID
	AND source_c = 1
	AND source_approved = 1
	WHERE article_region = @region
	AND article_posted > GetDate() - 60 
	ORDER BY f.WeightedRank DESC
END

Open in new window

cnxmaxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
Need to see your nested code... no reason not to nest that I can think of.
0
cnxmaxAuthor Commented:
I'm not very familiar with doing nesting like this and I was getting errors referring to duplicate columns. Here's what I tried.
ALTER PROCEDURE [dbo].[getMatchingArticles]
        @region varchar(50),
        @terms varchar(100)
AS
BEGIN
        SELECT * FROM (
        	SELECT TOP 80 article_ID, f.WeightedRank, article_title, article_tags, article_description, 
        	article_link, article_image, source_ID, source_title, source_url, article_posted, article_image
        	FROM table_articles INNER JOIN
        	(
        	        SELECT [KEY], SUM(Rank) AS WeightedRank
	                FROM
	                (
	                        SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_title, @terms)
	                        UNION
	                        select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_tags, @terms)
	                        UNION
	                        select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(table_articles, article_description, @terms)
	                ) as x          
	                GROUP BY [KEY]
	        ) as f
	        ON article_ID = f.[KEY]
	        JOIN table_sources 
	        ON article_listing = source_ID
	        AND source_c = 1
	        AND source_approved = 1
	        WHERE article_region = @region
	        AND article_posted > GetDate() - 60 
	        ORDER BY f.WeightedRank DESC
	)
	ORDER BY article_posted DESC
END

Open in new window

0
Dale BurrellDirectorCommented:
I need to see the errors since I don't have the tables to duplicate them.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale BurrellDirectorCommented:
The first error I can see is that you haven't given you outer most query an alias i.e.

  ORDER BY f.WeightedRank DESC
) as TableNameX
ORDER BY article_posted DESC
0
cnxmaxAuthor Commented:
When I nest it I get this error:

          Incorrect syntax near the keyword 'ORDER'.

If I take the ORDER BY off and just leave the select nested I get this error:

          Incorrect syntax near the keyword 'END'.
0
Dale BurrellDirectorCommented:
OK - thats the error I just posted about
0
cnxmaxAuthor Commented:
When I add the alias I get this error:

The column 'article_image' was specified multiple times for 'TableNameX'.
0
Dale BurrellDirectorCommented:
That should be a clue then! :)

  SELECT TOP 80 article_ID, f.WeightedRank, article_title, article_tags, article_description,
    article_link, article_image, source_ID, source_title, source_url, article_posted, article_image

should be

  SELECT TOP 80 article_ID, f.WeightedRank, article_title, article_tags, article_description,
    article_link, article_image, source_ID, source_title, source_url, article_posted
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cnxmaxAuthor Commented:
ah yes... thank you for pointing out the obvious to me :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.