I am trying to update the ColumnNo field to display the same number when the AppID and FootNote are the same. I need it to display like the first image below. I have tried Which works in reverse. And some other functions to no avail. How can I flip this? Answer Use dense_rank():
Tag: tsql
Create a table on the SQL server with the data from an SQL query?
Is it possible to create a table on the SQL Server with the data from an SQL query? I am trying to make a data filter screen in an SQL database and I want to try to filter a result that has already been filtered before If you help me with an example I appreciate. Answer In SQL Server you
How to use ‘Count’, based on a particular column in SQL
I have a query and which give the count greater than 1, but what I expect is I need the result to be based on particular column(Rollno.) How to achieve it. Table Studies The below query gives me Count but not as expected The query gave me the below result But the expected result is As you can see the
SQL Subquery within a subquery [closed]
I am writing a nested SQL where I need to have a subquery within a subquery Let’s say I am trying to retrieve a row from ORDERS that matches MAIN_ID from SOURCE that matches MODULE from MODULES. …
Calculate cumulative product value
I have the following database table: Date Return Index 01-01-2020 0.1 Null 01-02-2020 0.2 Null 01-03-2020 0.3 Null I would like to update the Index value using the following …
Calculate cumulative value in update query using SQL
I have the following database table: Date Return Index 01-01-2020 0.1 Null 01-02-2020 0.2 Null 01-03-2020 0.3 Null I would like to update the Index value using the following …
SQL Use Value from different column on same Select
I have a question if this is possible which will save me time writing extra code and limits user error. I need to use a value from a column (which has already performed some calulcation) from the same select then do extra calculation on it. I encounter this a lot in my job. I will highlight the problem with a
Split text into multiline chunks based on fixed character length using SQL
I am looking for SQL that takes a string of text (coming in as up to 3 lines, but usually 1) and breaks it into at most 3 lines with a max of 30 characters per line. (The background is I’m trying to insert text into address line fields and sometimes the incoming values are longer than 30 characters and
SQL with non-existing column in where cause can be executed
Here’s my SQL The issue is, the colA1 does not exist in TableB. So if I just run this Then SQL Server Management Studio will return an error: Invalid column name ‘colA1’. But if it is in the where cause, the first SQL can be executed as if where cause is always true. Answer Thats because SQL…
How to limit STUFF function within CTE for removing character before recurring word
A follow up question for Replace a recurring word and the character before it Credit to @Larnu for the following code, how would this code be modified to not produce a null value if there is no longer a character before a recurring word, “[BACKSPACE]”. I believe the null value is occurring because…