The following queries can be used to return duplicate rows in SQLite.
Here, the duplicate rows contain duplicate values across all columns, including the ID column.
Continue readingCategory: SQLite
5 Ways to Select Rows with the Maximum Value for their Group in SQL
Here are five options for using SQL to return only those rows that have the maximum value within their group.
These examples work in most major RDBMSs, including MySQL, MariaDB, Oracle, PostgreSQL, SQLite, and SQL Server.
Continue reading3 Ways to Select the Row with the Maximum Value in SQL
Here are three examples that use SQL to find and select the row with the maximum value in a given column.
The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.
Continue reading2 Ways to Delete Duplicate Rows in SQLite
The following options can be used to delete duplicate rows in SQLite.
These examples delete duplicate rows but keep one. So if there are three identical rows for example, it deletes two of them and keeps one. This is often referred to as de-duping the table.
Continue readingHow to Get Multiple Rows into a Comma Separated List in SQL
Most of the major RDBMSs have a function that enables us to return our query results as a comma separated list.
That is, we can use such a function to convert each row into a separate list item, within a comma separated list.
Below are examples of how to achieve this in some of the more popular RDBMSs.
Continue readingHow to Create a Table Only if it Doesn’t Exist in SQLite
In SQLite, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table or view of the same name already exists in the database before creating it.
Creating a table without this clause would normally result in an error if a table of the same name already existed in the database. But when using the IF NOT EXISTS clause, the statement has no effect if a table already exists with the same name.
Return Rows that Contain Alphanumeric Characters in SQLite
Here’s an example of returning rows that contain alphanumeric characters in SQLite.
Alphanumeric characters are alphabetic characters and numeric characters.
Continue readingFind Values That Don’t Contain Numbers in SQLite
The following example returns all rows that don’t contain any numbers in SQLite.
By “number” I mean “numerical digit”. Numbers can also be represented by words and other symbols, but for the purpose of this article, we’re returning values that don’t contain any numerical digits.
Continue reading2 Ways to Return Just the Numeric Values from a SQLite Database Column
The following SQLite examples return only those rows that have numeric values in a given column.
Continue readingHow to Check a Column’s Data Type in SQL
In SQL, the columns information schema view, which returns information about columns, is an ISO standard view that is supported by most of the major RDBMSs. You can use this view to get information about a column’s data type.
Most of the major RDBMs also provide other methods for getting column information.
Here are examples of getting the data type of a column in various SQL databases.
Continue reading