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
How can I avoid ResultSet is closed exception in Java?
Sounds like you executed another statement in the same connection before traversing the result set from the first statement. If you’re nesting the processing of two result sets from the same database, you’re doing something wrong. The combination of those sets should be done on the database side.
When to use a left outer join?
Joins are used to combine two related tables together. In your example, you can combine the Employee table and the Department table, like so: SELECT FNAME, LNAME, DNAME FROM EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DNO=DEPARTMENT.DNUMBER This would result in a recordset like: FNAME LNAME DNAME —– —– —– John Smith Research John Doe Administration I … Read more
Getting the Last Insert ID with SQLite.NET in C#
Using C# (.net 4.0) with SQLite, the SQLiteConnection class has a property LastInsertRowId that equals the Primary Integer Key of the most recently inserted (or updated) element. The rowID is returned if the table doesn’t have a primary integer key (in this case the rowID is column is automatically created). See https://www.sqlite.org/c3ref/last_insert_rowid.html for more. As … Read more