Access the “previous row” value in a SELECT statement
Use the lag function: SELECT value – lag(value) OVER (ORDER BY Id) FROM table Sequences used for Ids can skip values, so Id-1 does not always work.
sql
Is it possible to insert into two tables at the same time?
In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more
What is a SQL JOIN, and what are the different types? [duplicate]
What is SQL JOIN ? SQL JOIN is a method to retrieve data from two or more database tables. What are the different SQL JOINs ? There are a total of five JOINs. They are : 1. JOIN or INNER JOIN 2. OUTER JOIN 2.1 LEFT OUTER JOIN or LEFT JOIN 2.2 RIGHT OUTER JOIN … Read more
Delete duplicate rows keeping the first row
I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
What is the difference between JOIN and INNER JOIN?
They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.
How liberal should I be with NOT NULL columns?
Honestly, I’ve always thought NOT NULL should be the default. NULL is the odd special case, and you should make a case for it whenever you use it. Plus it’s much easier to change a column from NOT NULL to nullable than it is to go the other way.
single fixed table with multiple columns vs flexible abstract tables
Certain issues need to be clarified and resolved before we can enter into a reasonable discussion. Pre-requisite Resolution Labels In a profession that demands precision, it is important that we use precise labels, to avoid confusion, and so that we can communicate without having to use long-winded descriptions and qualifiers. What you have posted as … Read more
SQL problem with error “Invalid data type”
I know this is an old post but since I encountered the same issue and was able to solve it, thought of sharing it. This is an IntelliSense cache issue and could be solved by pressing ctrl+shift+R (shortcut for Edit -> IntelliSense -> Refresh Local Cache)
How would I find the second largest salary from the employee table? [closed]
Try this: SELECT max(salary) FROM emptable WHERE salary < (SELECT max(salary) FROM emptable);
List of differences between SQL databases
I’m not sure how comprehensive this list is, but maybe this will help – http://troels.arvin.dk/db/rdbms/