I am using SQL Server, and I have a table that looks like this: I want to update id to have a sequence of rows ordered by distance asc, then i asc I tried this but I am getting an error Incorrect syntax near “order” How to fix that? Answer You need to use ROW_NUMBER inside a derived table or
Tag: sql-server
How to update USING sum and subquery
I have a SQL Server table similar to this: InkitemNo CapacityUnit NewInk OldInk ReturnInk ProdQty Description UsedInk 204 Machine1 5 2 0 4000 Next ? 223 machine2 4 3 1 8000 NULL ? 204 Machine2 0 0 0 5000 Next ? 224 Machine2 4 0 2 3000 Next ? I’m trying to write a query with this formula: Example to
How to use refer to multiple id’s when using JOINs in SQL
I try to display the number of reviews which an employee provided and the number of reviews which he received. I try to do something like this, but in my output table (3) I receive incorrect data. I believe that this happens due to the wrong reference: however, if I reference it like this: then nr_of_reviews_…
Group-by and coalesce cannot be combined, gives an error?
I am creating an SSRS report and while making the data queries to it I got into this error. I do need the data set (Month to Date and Year to Date values) in one row connected with a group by command. This is the crystal report I’m referring to and converting to SSRS. The error I get is something
How to not lose unicode characters in Stored Procedure parameter
Problem Statement: We are having a legacy application with backend as SQL Server. Till now, we did not face any issues in passing non-unicode values. Now, we are getting unicode characters from user interface. The unicode character is getting passed as given below, in UI. These data are being inserted into ta…
How to get in which store did the specific product get the most benefit?
So, here are my tables: Sales id product_code store_id 1 4536 1 2 4674 2 Products product_code product_name price real_price 4536 Red bull energy drink 300 ml 3,68 2,88 4674 Mac coffee 25 gr 2,59 2,10 Stores store_id store_name 1 first 2 second I need to find in which store the product ‘red bull’ …
Cursor is storing values in a variable but not updating in another table Sql Server
I have write a cursor in which i am getting values from a table using cursor and updating these values in another table . While testing i have analysed that values are storing in table and update is also working because sql server message shows that 1 row has been update but the values from cursor are not bei…
SQL query to replace multiple occurrences with the most frequent ones
I have a products table, which has the product description in two languages, one in English and one in an alternate language. Let’s say: Product_Desc Product_Desc_Alt A A1 A A2 A A1 A A3 B B1 B B2 B B2 C C1 If I do a GROUP BY statement, there are multiple alternate language occurrences for the same prod…
Select where all columns in list item
How do I do the equivalent of the following in SQL Server? I only want to match rows where There can be an arbitrary number of match conditions. The only solution I have been able to find is the above, but doubt it would scale to 1000s of dynamic values. Answer Put those value in temp table or use Table
Create database scripts where tables have prepopulated data across environment
I have a table named, ‘[UserTypes]’ where Primary id is [uniqueidentifier]. and I have a reference in another table called ‘[Users]’. Users table has UserTypeId as foreignKey. This ’80D1EEE7-0BCC-48A7-A741-29A1D8B6E580′ is the userTypeId of ‘ADMI’ from the userT…