The SQL standard (current version is ISO/IEC 9075:2011, in multiple parts) says nothing about the ‘back-tick’ or ‘back-quote’ symbol (Unicode U+0060 or GRAVE ACCENT); it doesn’t recognize it as a character with special meaning that can appear in SQL. The Standard SQL mechanism for quoting identifiers is with delimited identifiers enclosed in double quotes: SELECT … Read more
You have two options for concatenating strings in Oracle: CONCAT Using || CONCAT example: CONCAT( CONCAT( CONCAT( CONCAT( CONCAT(‘I like ‘, t.type_desc_column), ‘ cake with ‘), t.icing_desc_column), ‘ and a ‘), t.fruit_desc_column) Using || example: ‘I like ‘ || t.type_desc_column || ‘ cake with ‘ || t.icing_desc_column || ‘ and a ‘ || t.fruit_desc_column
create table my_table ( column_a integer not null, column_b integer not null, column_c varchar(50), primary key (column_a, column_b) );
Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server … Read more
You want an outer join for this (and you need to use person as the “driving” table) SELECT person.person_id, COUNT(appointment.person_id) AS “number_of_appointments” FROM person LEFT JOIN appointment ON person.person_id = appointment.person_id GROUP BY person.person_id; The reason why this is working, is that the outer (left) join will return NULL for those persons that do not … Read more
SELECT T1.ID, T1.AccountNumber, T1.Date, MIN(T2.Date) AS Date2, DATEDIFF(“D”, T1.Date, MIN(T2.Date)) AS DaysDiff FROM YourTable T1 LEFT JOIN YourTable T2 ON T1.AccountNumber = T2.Accountnumber AND T2.Date > T1.Date GROUP BY T1.ID, T1.AccountNumber, T1.Date; or SELECT ID, AccountNumber, Date, NextDate, DATEDIFF(“D”, Date, NextDate) FROM ( SELECT ID, AccountNumber, Date, ( SELECT MIN(Date) FROM YourTable T2 WHERE T2.Accountnumber … Read more
There is one tricky scenario. If I am looking for ’40’ in the list ‘17,34,400,12’ then it would find “,40” and return that incorrect entry. This takes care of all solutions: WHERE (‘,’ + RTRIM(MyColumn) + ‘,’) LIKE ‘%,’ + @search + ‘,%’
You can take advantage of the “deleted” pseudo table in this example. Something like: begin transaction; declare @deletedIds table ( id int ); delete from t1 output deleted.id into @deletedIds from table1 as t1 inner join table2 as t2 on t2.id = t1.id inner join table3 as t3 on t3.id = t2.id; delete from t2 … Read more
If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db’s that can be attached is a compile time setting(SQLITE_MAX_ATTACHED), currently defaults to 10, but this too may vary by … Read more
COUNT(*) counts all rows COUNT(column) counts non-NULLs only COUNT(1) is the same as COUNT(*) because 1 is a non-null expressions Your use of COUNT(*) or COUNT(column) should be based on the desired output only.