Sql notes

From raju

duplicates related

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

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