Priority of a Query in Ms SQL

Priority of a query in MS SQL

Not in versions below SQL 2008. In SQL Server 2008 there's the resource governor. Using that you can assign logins to groups based on properties of the login (login name, application name, etc). The groups can then be assigned to resource pools and limitations or restrictions i.t.o. resources can be applied to those resource pools

MSSQL query based on column priority

I suspect that you want:

select top (1) *
from mytable
where isverified = 1 and isrejected = 0
order by score1 desc, score2 desc, score3 desc

The query filters on verified and non-rejected rows. Then, we keep the row that has the highest score1; if there are ties, we compare score2, and then score3.

SQL queries priority of Math

You are listing PHOUR and COMISSION column as AVG, but using as SUM at the total.
Your total column formula should be like this.

 ( SUM(CAST(dbo.TBL_NEWDAY.HOURS AS FLOAT)) *
AVG(CAST(dbo.TBL_NEWEMPLOYEE.NEPHOUR AS FLOAT)) )
+ ( SUM(CAST(dbo.TBL_NEWDAY.SALES AS FLOAT)) / 100 )
* AVG(CAST(dbo.TBL_NEWEMPLOYEE.NECOMISSION AS FLOAT)) AS TOTAL

Set priority criteria in rows in Query

SOLVED: from the posted problem, I've created series of queries. from the raw source table I've created 1st: Material with Unique Count (done this with criteria 1) 2nd: Material with NO SG division and 3rd: Material with SG division,

with having this code under the query of Material with NO SG

`SCOPE: IIf([Query_SC_and_Purchase_Price_Scope_SG].[SCOPE] Is Null,[Query_SC_and_Purchase_Price_Scope_NO_SG].[SCOPE],[Query_SC_and_Purchase_Price_Scope_SG].[SCOPE])`

This for Material with SG Code query

   SCOP: IIf([Query_SC_and_Purchase_Price_Scope_SG].[SCOPE] Is Null,[Query_SC_and_Purchase_Price_Scope_NO_SG].[SCOPE],[Query_SC_and_Purchase_Price_Scope_SG].[SCOPE])

Gathering all those datas and checking I've created a UNION ALL query

 `SELECT Query_SC_and_Purchase_Price_Scope_Unique.* 
FROM Query_SC_and_Purchase_Price_Scope_Unique
UNION ALL
SELECT Query_SC_and_Purchase_Price_Scope_SGNOSG_SG.*
FROM Query_SC_and_Purchase_Price_Scope_SGNOSG_SG
UNION ALL SELECT Query_SC_and_Purchase_Price_Scope_SGNOSG_NOSG.*
FROM Query_SC_and_Purchase_Price_Scope_SGNOSG_NOSG;
`

I've come with my correct values.

Priority queue in SQL Server

Please read Using tables as Queues. The important issues:

  • You must organize the table according to the dequeue strategy. Primary key in IDENTITY makes absolutely no sense. Use a clustered index based on priority and dequeue order.
  • You must dequeue atomically in a single statement, use DELETE ... OUTPUT ...

So it should be something along these lines:

CREATE TABLE PriorityQueue
(
priority int not null,
enqueue_time datetime not null default GETUTCDATE(),
absolute_url varchar (8000) not null,
depth int not null,
domain_host varchar (255) not null,
);

CREATE CLUSTERED INDEX PriorityQueueCdx on PriorityQueue(priority DESC, enqueue_time);

CREATE PROCEDURE dbo.Dequeue
AS
BEGIN
with cte as (
SELECT top 1 absolute_url, depth, priority
FROM [PriorityQueue] with (rowlock, readpast)
ORDER BY priority DESC, enqueue_time)
DELETE FROM cte
OUTPUT DELETED.*;
END
GO

Pick column values based upon the priority in sql server

Check this below option.

DEMO HERE

WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY ID,CAST(Date+'-2019' AS DATE)) RN
FROM your_table
)

SELECT ID,COL2,COL3,date,
(
SELECT TOP 1 COL3
FROM CTE B
WHERE B.RN <= A.RN
AND B.ID = A.ID
ORDER BY (CASE WHEN B.COL2 = 'CLINICAL' THEN 1 ELSE 2 END),
B.RN DESC
) Col4
FROM CTE A

Filtering with priority criteria in SQL server

you can use row_number() to assign the priority per mCode and then just filter the one with highest priority

select *
from
(
select *, p = row_number() over (partition by mCode
order by case when pCode = 'D' then 1
when pCode = 'L' then 2
when pCode = 'E' then 3
when pCode = 'O' then 4
end)
from Table1
) d
where d.p = 1


Related Topics



Leave a reply



Submit