For each date and id we have to give the rank to values in decreasing order. Below is the Input Table. Output should look like below: Answer You can use RANK(), partitioning on date and id and ordering by value descending: Output: Demo on SQLFiddle This query will run on all the DBMS you have tagged your ques…
Tag: sql-server
Delete from child table with two parents without cascade delete in sql server
I need your guidance. I create a script to delete from 3 tables separately. meaning when I created the script is to delete from table3 (child) then delete from 2 (child), then delete from table1(parent) I am having problem with Table3 since table2 has foreign key both for table2 and table1 so I cannot delete …
Converting SQL query result to XML [closed]
Input data id year Name provid prov 1 1995 MAC 1995-11_CL236 reg 236 1 1995 MAC 1995-11_CL230 reg 230 (1) 1 1995 MAC 1995-11_CL229J reg 229J 1 1995 MAC 1995-11_CL260 …
String or binary data would be truncated when using it in a stored procedure
I create this TYPE table in database: CREATE TYPE [dbo].[AccessLevel] AS TABLE ( [RoleId] [INT] NULL, [Access] [NVARCHAR](1) NULL, [IsDelete] [BIT] NULL ) GO and I need to insert …
Arranging data by year and proportion
I have one table with the following format: Note 1: [End_Date = NULL] means that participation has not ended. [Proportion = NULL] means that participation is 0. Output: I want to have the proportion of each sub_entity for every active year. Note 2: One Sub_Entity can be a proportion of different Entities. The…
High performance query to get datetime when value was last changed
The data I am working with Consider the following 2 database tables: CREATE TABLE [dbo].[Contact]( [ID] [int] IDENTITY(1,1) NOT NULL, [Contact_UID] [uniqueidentifier] NOT NULL CONSTRAINT […
Group by question in SQL Server, migration from MySQL
Failed finding a solution to my problem, would love your help. ~~ Post has been edited to have only one question ~~- Group by one query while selecting multiple columns. In MySQL you can simply group by whatever you want, and it will still select all of them, so if for example I wanted to select the newest 10…
SQL join table with 12 column composite PK with itself painlessly
I have a table with a composite natural primary key that involves 12 columns (c1, c2, …, c12). I want to write a self join query: This join is a pain to write. Is there a better way? Answer Personally, if you must have a 12 column “key”, I would DROP that key, and create a computed column th…
How to use temp table in SQL Server script?
I have to use some hardcode data in sql script, for this I am using temp table On line 6, it is showing error as Expecting Id Is there any simplest way to do this? Answer There is no double data type in sql server use decimal.
Need to Update based on ID and Date
I have the following SQL statement, which I think should update 1 field, using some pretty simple standard deviation logic, and based on ID and Date. I think the ID and Date has to be included to get …