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
Mssql Dynamic Pivot Column Values to Column Header
How to Create Query from Parent Child Hierarchy Table
How to Update a Blob in SQL Server Using Tsql
Does Ms Access Suppress Primary Key Violations on Inserts
Replace Row Value with Empty String If Duplicate
Linq Orderby. Does It Always Return the Same Ordered List
Pyodbc Execute Command Not Accepting? Parameters Correctly
Query for Searching the Name Alphabetically
Timezone Date Format in Oracle
How to Query All Rows Within a 5-Mile Radius of My Coordinates
Performance and Readability of Regexp_Substr VS Instr and Substr
Remove Duplicate Rows in a Table
Stop Access from Using Wrong Identity When Appending to Linked Table on SQL Server