SQL Server - SELECT statement with Variable and SUM() method

Question

EDIT:

Will try to explain in more detail this time as best as I can. I tried to make the query a bit simpler thinking it would make it easier to understand but it might have been a bad move.

I'm trying to get the PK_Queue and FK_Queue_Milestone from the 1st row of my Queue table ordered by PriorityScore DESC and TimeAdded ASC

I only want to get the first row, but I was advised to not use TOP(1) because it would result to another SELECT being made to my original select.

This is the query that I have:

SELECT
@Local_PK_Queue = Q.PK_Queue,
@Local_PK_Milestone_Validate = Q.FK_Queue_Milestone

FROM dbo.Queue AS Q 

INNER JOIN @Local_PKHolderTable AS P
ON Q.FK_Queue_Process = P.PK_Process
AND Q.FK_Queue_Milestone = P.PK_Milestone
AND Q.FK_Queue_QueueType = P.PK_QueueType

WHERE Q.FK_Queue_Milestone = P.PK_Milestone
AND Q.FK_Queue_Process = P.PK_Process
AND Q.Tags LIKE '%' + @Input_Tags + '%'
AND ((FK_Queue_State = 5 AND TimeDeferred < GETUTCDATE()) OR (FK_Queue_State = 1))
AND Q.FK_Queue_Robot IS NULL
AND Q.FK_Queue_QueueType = P.PK_QueueType

ORDER BY
Q.PriorityScore DESC,
Q.TimeAdded

When I try to run the query, it doesn't seem to be ordering it properly because it always gets the last row of my table.

So did some research and stumbled upon this question here.

It seems to be the same problem that I am experiencing but using MySQL instead of SQL Server.

TLDR: Want to ORDER BY Priority Score DESC and TimeAdded, but is not working properly

_In a separate post here, I read that I need to use the SUM command in order to ORDER BY int columns._ Could you link to that post as this statement doesn't make much sense to me? — Bill Tür stands with Ukraine, Jan 17 '19 at 11:31
It is because you are using SUM which is an aggregate that you need to group the data by PK_Test. — arahman, Jan 17 '19 at 11:31
what do you want to happen? Give more details and example with a small amount of data — Cato, Jan 17 '19 at 11:33
You should explain the original problem instead of problems you're having with the solution. Perhaps you just want a `TOP 1` and not the SUM. — Salman A, Jan 17 '19 at 11:36

score 0 · Answer 1 · answered Jan 17 '19 at 11:31

Well, you would write this as:

SELECT @var = PK_Test, @var2 = SUM(PriorityScore)
FROM Queue
GROUP BY PK_Test
ORDER BY SUM(PriorityScore);

This is very strange, though, because the GROUP BY presumably returns multiple rows and you presumably want only one. I might suspect that you really want to assign the variables to the highest priority scores:

SELECT TOP (1) @var = PK_Test, @var2 = SUM(PriorityScore)
FROM Queue
GROUP BY PK_Test
ORDER BY SUM(PriorityScore) DESC;

SQL Server - SELECT statement with Variable and SUM() method

1 Answers1