I accomplished the same goal by using a WITH clause, it’s nowhere near as elegant but can do the same thing. Though for this example it’s really overkill. I also don’t particularly recommend this. WITH myconstants (var1, var2) as ( values (5, ‘foo’) ) SELECT * FROM somewhere, myconstants WHERE something = var1 OR something_else … Read more
You want to find top n rows per group. This answer provides a generic solution using example data that is different from OP. In MySQL 8 or later you can use the ROW_NUMBER, RANK or DENSE_RANK function depending on the exact definition of top 5. Below are the numbers generated by these functions based on … Read more
You can do with the –no-data option with mysqldump command mysqldump -h yourhostnameorIP -u root -p –no-data dbname > schema.sql
They are not the same thing. Consider these queries: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 and SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 The first will return an order and its lines, if any, for order number 12345. The second will return … Read more
SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1 Simply group on both of the columns. Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of “functional dependency”: In relational database theory, a functional dependency is a … Read more
Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more
I know most databases have restrictions, but Postgres doesn’t. You can run any number table creations, column changes and index changes in a transaction, and the changes aren’t visible to other users unit COMMIT succeeds. That’s how databases should be! 🙂 As for SQL Server you can run DDL inside of a transaction, but SQL … Read more
It depends on the database. MySQL has an extension where INT(N) means an INT with a display width of 4 decimal digits. This information is maintained in the metadata. The INT itself is still 4 bytes, and values 10000 and greater can be stored (and probably displayed, but this depends how the application uses the … Read more
This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement. According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least … Read more