SQL statement does not select
I am using SQL Server in a C # project for a troubleshooter and I have a table containing ID, Question, QuestionId, Solution and Rank. I want you to have several solutions to a problem, and the program will choose the best ranked solution, which is chosen only by the largest number, which increases each time it is correct. For this I have the following SQL statement:
sql = "SELECT Solution FROM dbo.Questions WHERE Rank=(SELECT MAX(Rank) FROM
dbo.Questions) AND QuestionId =" + questionId;
When I only had one solution it worked fine, but when I have multiple solutions it doesn't.
source to share
You need to parameterize your queries correctly. Bobby Tables: A Guide to Preventing SQL Injection
sql = "SELECT Solution FROM dbo.Questions q WHERE Rank=(SELECT MAX(Rank) FROM
dbo.Questions i where i.QuestionId = q.QuestionId) AND q.QuestionId =" + questionId;
This ensures that the max(rank)
subquery returned is the max(rank)
solution for QuestionId
which you are querying.
You can also do this if you only want one Solution
:
select top 1 Solution
from dbo.Questions q
where QuestionId = @QuestionId
order by [Rank] desc
source to share
You choose the maximum rank of all solutions and look for a solution to the question asked with that rank.
Start at the beginning - select possible solutions in CTE
with solutions as(
SELECT Solution, Rank FROM dbo.Questions WHERE QuestionId = @questionId
)
... more to come
Considering that you can use the ranking function to rank all possible solutions by rank and choose the one with the best rank (too many ranks in this proposal!)
with solutions as(
SELECT Solution, Rank() OVER (ORDER BY Rank DESC) as rank
FROM dbo.Questions WHERE QuestionId = @questionId
)
SELECT * FROM solutions WHERE rank = 1
This does not cover 2 solutions with equal rank - you will get 2 query results if 2 have the same rank. Two solutions are out there - consider using both.
- Select the tiebreaker column - probably the most recent one - by adding a second column to
ORDER BY
(...RANK() OVER (ORDER BY rank DESC, CreatedDate DESC
) - Throw
TOP 1
in the final selection (SELECT TOP 1 * FROM solutions WHERE rank = 1
)
source to share
You can try this, I left the where clause outside the CTE query so that you can also use the query to get a complete list of each question with the highest rank (unless you specify the questionId filter):
with [ctreMaxSolution] as
(
select [QuestionId]
, max([Rank]) as [Rank]
from [dbo].[Questions]
group by [QuestionId]
)
select *
from [dbo].[Questions] as [q]
inner join [cteMaxSolution] as [cms] on [q].[QuestionId] = [cms].[QuestionId]
and [q].[Rank] = [cms].[Rank]
where [q].[QuestionId] = @questionId;
I used a SQL Server variable there, but you can make a stored procedure out of it, or convert it into a custom query like in your question, completely for you.
source to share