How to use NULL or empty string in SQL
Select * From Table Where (col is null or col=””) Or Select * From Table Where IsNull(col, ”) = ”
sql
Getting only Month and Year from SQL DATE
As well as the suggestions given already, there is one other possiblity I can infer from your question: – You still want the result to be a date – But you want to ‘discard’ the Days, Hours, etc – Leaving a year/month only date field SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field] FROM <your_table> … Read more
SQL, Postgres OIDs, What are they and why are they useful?
OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That’s handy for tables where you don’t have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of … Read more
Escaping keyword-like column names in Postgres
Simply enclose year in double quotes to stop it being interpreted as a keyword: INSERT INTO table (id, name, “year”) VALUES ( … ); From the documentation: There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes (“). A delimited … Read more
How to retrieve the current value of an oracle sequence without increment it?
SELECT last_number FROM all_sequences WHERE sequence_owner=”<sequence owner>” AND sequence_name=”<sequence_name>”; You can get a variety of sequence metadata from user_sequences, all_sequences and dba_sequences. These views work across sessions. EDIT: If the sequence is in your default schema then: SELECT last_number FROM user_sequences WHERE sequence_name=”<sequence_name>”; If you want all the metadata then: SELECT * FROM user_sequences WHERE … Read more
Postgresql query between date ranges
With dates (and times) many things become simpler if you use >= start AND < end. For example: SELECT user_id FROM user_logs WHERE login_date >= ‘2014-02-01’ AND login_date < ‘2014-03-01’ In this case you still need to calculate the start date of the month you need, but that should be straight forward in any number … Read more
Oracle “(+)” Operator
That’s Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn’t standardize OUTER joins. The query would be re-written in ANSI-92 syntax as: SELECT … FROM a LEFT JOIN b ON b.id = a.id This link is pretty good at explaining the … Read more
What is this operator in MySQL?
TL;DR It’s the NULL safe equal operator. Like the regular = operator, two values are compared and the result is either 0 (not equal) or 1 (equal); in other words: ‘a’ <=> ‘b’ yields 0 and ‘a’ <=> ‘a’ yields 1. Unlike the regular = operator, values of NULL don’t have a special meaning and … Read more
How to find current transaction level?
Run this: SELECT CASE transaction_isolation_level WHEN 0 THEN ‘Unspecified’ WHEN 1 THEN ‘ReadUncommitted’ WHEN 2 THEN ‘ReadCommitted’ WHEN 3 THEN ‘Repeatable’ WHEN 4 THEN ‘Serializable’ WHEN 5 THEN ‘Snapshot’ END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions where session_id = @@SPID learn.microsoft.com reference for the constant values.
Removing duplicate rows from table in Oracle
Use the rowid pseudocolumn. DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.