I have a table like this: I want to copy the last 3 values and at the end my table will look like this: Is it possible? Answer Use a self-join: If there are gaps in the ID space, generate gapless IDs with the window function row_number(). I do that in a CTE, because I am going to reuse the
Tag: sql
sql select query in same table
i have a newby sql related question. let’s say i have this simple table: i’d like to query for records which have “counterparts” only, i.e. i want to get a b only if there is b a in the table but i want to skip the “back links” (which is b ahere). to sum up i’d like t…
UPDATE syntax in SQLite
I need to know if I can do this in an UPDATE statement: Or similar syntax. I’m using SQLite. Note: Nobody understands me, I just want to know if it is possible to SET separate fields to separate values. That’s all. Answer There is a (standard SQL) syntax that is similar to what you propose but as …
sql query if parameter is null select all
Can the following query be modified to return all records if the ? is null? Answer You can also use functions IFNULL,COALESCE,NVL,ISNULL to check null value. It depends on your RDBMS. MySQL: or ORACLE: SQL Server / SYBASE:
PHP, ODBC, and SQL Injection
How does one prevent against SQL injections when using ODBC to connect to a MS SQL Server? odbc_prepare() doesn’t work (see my open question) due to bugs unixODBC, and there is no _escape or _quote …
Join a count query on generate_series() and retrieve Null values as ‘0’
I want to count ID’s per month using generate_series(). This query works in PostgreSQL 9.1: This is my output: But what I want to get is this output (‘0’ value in January): Months without id should be listed nevertheless. Any ideas how to solve this? Sample data: Answer Untangled, simplified…
SQL versus noSQL (speed)
When people are comparing SQL and noSQL, and concluding the upsides and downsides of each one, what I never hear anyone talking about is the speed. Isn’t performing SQL queries generally faster than performing noSQL queries? I mean, for me this would be a really obvious conclusion, because you should al…
Multiple conditions on the same column in the WHERE clause
I have a table something like this – How do I return the names of users where PropertyID = 13 AND PropertyVal=’Business Development Analyst’AND PropertyID = 11 AND PropertyVal = ‘Chicago’. How do I do multiple where clauses for the same column? Edit: I need the result set to look…
Defining the sort order of children in a hierarchy query
I would like to know the Oracle SQL query that orders the children in a hierarchy query by the sequence_within_parent column. An example data set and query are: create table tasks (task_id …
IIS connecting to LocalDB
Is there any way so IIS could connect to LocalDB without using the NT SERVICENETWORK SERVICE user account. This account has not suitable permissions. I’m looking use some other default account or …