Sql notes
From raju
show distinct and total counts
select count(distinct username) as distinct_cnt, count(username) as total_cnt from users
show duplicate values and their count
select username, email, count(*) from users group by username, email having count(*) > 1
Here we use 'having' instead of 'where' because the former can filter on aggregate functions.
Ref:- https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-sql-table/
show duplicated records
SELECT a.* FROM users a JOIN (SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING count(*) > 1 ) b ON a.username = b.username AND a.email = b.email ORDER BY a.email
Ref:- https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-sql-table/
dummy
String for SQL where clause
enclose each line in quotes
Use case 1:
head -n1 foo.csv | tr ',' '\n' | sed 's/\(.*\)/"\1"/g'
For example
% echo "foo1,foo2,bar1,bar2" | tr ',' '\n' | sed 's/\(.*\)/\tstructField("\1", ""),/g' structField("foo1", ""), structField("foo2", ""), structField("bar1", ""), structField("bar2", ""),
Use case 2:
% cat ~/bin/sqlin sed 's/\(.*\)/"\1"/g' | tr '\n' ',' | sed -e 's/^/(/g' -e 's/,$/)\n/g' % chmod +x ~/bin/sqlin
Sample input:
% cat foo.txt cusip 06739FHV6 655844AJ7 06738C828 61747YCM5 084664CE9
Sample usage:
% cat ~/x/foo.txt | tail -n +2 | ~/bin/sqlin ("06739FHV6","655844AJ7","06738C828","61747YCM5","084664CE9")
tags | quote string for sql in clause
convert date to YYYYMMDD form
Example 1:
convert(varchar, fld_date, 112) as 'my_fld_date'
Example 2:
select fld1, convert(varchar(10), fld_date, 112) fld_date, fld3, fld4 from table where fld1 in ('valA' ,'valB')
tags | get date as yyyymmdd
Ref:- https://www.w3schools.com/sql/func_sqlserver_convert.asp
convert date to YYYY-MM-DD
To convert a date to iso string in sybase ase
select str_replace( convert( varchar, ColumnName, 111), '/', '-') NewColumnName
tag | convert date to iso string
split a string
Situation:
One column in a sql table is of the form
field1|field2|field3|...|fieldN
Task:
Split this into two columns - every thing but the last field, last field.
Solution:
select substring(col_name, 1, len(col_name)-charindex('|', reverse(col_name))) all_but_last_field, substring(col_name, len(col_name)-charindex('|', reverse(col_name))+2, len(col_name)) last_field, * from table_name
Explanation:
- The length of the string, N, is given by len(col_name).
- The range of charindex is [1:N].
- If P is the position of the last delimiter when counted from end, its position would be N-P+1 when counted from the beginning.
- So, the fields that we want are [1:N-P],[N-P+2:N]
- P is given by charindex('|', reverse(col_name))
tags | split string sybase sql table, split a string and get the last field
Ref:- http://stackoverflow.com/questions/14412898/split-string-and-take-last-element
get most recent record
Say we have a table with three fields - cusip, as_of and quantity. New entries are added if and only if the quantity of a cusip changes from previous day. The task is to find the most recent quantity for a given date and a list of cusips.
Assume the list of cusips is given as "000324AA1","000361AH8", "00103YAE1","00130HBH7"
Assume the date of interest is '20160701'
The query would be something like
select a.cusip, convert(varchar(8), a.as_of, 112) as 'date', a.quantity from tbl a inner join ( select cusip, max(as_of) as as_of from tbl where cusip in ("000324AA1","000361AH8", "00103YAE1","00130HBH7") and convert(varchar(8), as_of, 112) <= '20160701' group by cusip ) b on a.cusip=b.cusip and a.as_of = b.as_of go
tags | query the latest record
Ref:- http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record
remove extra spaces at the end
SELECT RTRIM(column)
count number of entries per date
tags | check coverage by date
Task:- For all dates on or after 20180801, get the number of entries for each date
select tbl.date_field, count(tbl.date_field) as date_count from server..table WITH(NOLOCK) tbl where date_field >= '20180801' group by date_field order by date_field desc
stackoverflow links
External Links
- http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
- get the recent most match, join two tables but get the most recent record per group, greatest-n-per-group - https://stackoverflow.com/a/497557/6305733 , https://stackoverflow.com/a/2111420/6305733, https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column/123481#123481
- https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan - contains very good explanation on how to get query execution plan.
scope | sql server
RDBMS
- MySQL - open source RDBMS
- MS SQL Server / SQL Server - Microsoft product
limit clause
RDBMS | SQL Server or MS Access | MySQL | Oracle |
---|---|---|---|
Syntax | SELECT TOP number [PERCENT] column(s) FROM table WHERE condition; |
SELECT column(s) FROM table_name WHERE condition LIMIT number; |
SELECT column(s) FROM table_name WHERE ROWNUM <= number; |
Example | SELECT TOP 10 * FROM Customers; |
SELECT * FROM Customers LIMIT 10; |
SELECT * FROM Customers WHERE ROWNUM <= 10; |
Example 2 | SELECT TOP 10 PERCENT * FROM Customers; |
||
Example 3 | SELECT TOP 10 * FROM Customers WHERE Country='Germany'; |
SELECT * FROM Customers WHERE Country='Germany' LIMIT 10; |
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 10; |
Ref:- https://www.w3schools.com/sql/sql_top.asp - I just reformatted their entire page into a table so that it is easy to look and compare.
tags | ORA-00923: FROM keyword not found where expected
between clause
It is inclusive on both ends.
where x between a and b
means
a <= x <= b