In SQL Server: SELECT col1 AS [text()] FROM foo FOR XML PATH (”) In MySQL: SELECT GROUP_CONCAT(col1 SEPARATOR ”) FROM foo In PostgreSQL: SELECT array_to_string ( ARRAY ( SELECT col1 FROM foo ), ” ) In Oracle: SELECT * FROM ( SELECT col1, ROW_NUMBER() OVER(ORDER BY 1) AS rn FROM foo MODEL DIMENSION BY (rn) … Read more
In Mysql there is a cheap trick to do this: mysql> select ~0; +———————-+ | ~0 | +———————-+ | 18446744073709551615 | +———————-+ the tilde is the bitwise negation. The resulting value is a bigint. See: http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert For the other integer flavours, you can use the right bitshift operator >> like so: SELECT ~0 as max_bigint_unsigned … Read more
The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses. FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be … Read more
You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more
I had a similar problem with several lookup tables joining to a large table with all id fields indexed. To monitor the effect of the joins on query time execution, I ran my query several times (limiting to first 100 rows), adding a Join to an additional table each time. After joining 12 tables, there … Read more
Yes – you can have a foreign key that references a unique index in another table. CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn) ALTER TABLE dbo.YourChildTable ADD CONSTRAINT FK_ChildTable_Table FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)
For a single keyword on VARCHAR fields you can use LIKE: SELECT id, category, location FROM table WHERE ( category LIKE ‘%keyword%’ OR location LIKE ‘%keyword%’ ) For a description you’re usually better adding a full text index and doing a Full-Text Search (MyISAM only): SELECT id, description FROM table WHERE MATCH (description) AGAINST(‘keyword1 keyword2’)
You need to cast the text as binary (or use a case-sensitive collation). With temp as ( select ‘Test’ as name UNION ALL select ‘TEST’ UNION ALL select ‘test’ UNION ALL select ‘tester’ UNION ALL select ‘tester’ ) Select Name, COUNT(name) From temp Group By Name, Cast(name As varbinary(100)) Using a collation: Select Name Collate … Read more
Basically because it wasn’t possible to retroactively change how views work. I’d like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists. You can filter access to the view its self with row security normally. The tables accessed by the view will … Read more
Mostly you should do nothing about it. It is possible to disable the warning by setting ansi_warnings off but this has other effects, e.g. on how division by zero is handled and can cause failures when your queries use features like indexed views, computed columns or XML methods. In some limited cases you can rewrite … Read more