Grouped LIMIT in PostgreSQL: show the first N rows for each group?
New solution (PostgreSQL 8.4) SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r, t.* FROM xxx t) x WHERE x.r <= 2;
sql
Finding duplicate rows in SQL Server
select o.orgName, oc.dupeCount, o.id from organizations o inner join ( SELECT orgName, COUNT(*) AS dupeCount FROM organizations GROUP BY orgName HAVING COUNT(*) > 1 ) oc on o.orgName = oc.orgName
How to drop all tables from a database with one SQL query?
Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL: DECLARE @sql NVARCHAR(max)=” SELECT @sql += ‘ Drop table ‘ + QUOTENAME(TABLE_SCHEMA) + ‘.’+ QUOTENAME(TABLE_NAME) + ‘; ‘ FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ Exec Sp_executesql @sql Sys.Tables Version DECLARE @sql … Read more
What did MongoDB not being ACID compliant before v4 really mean?
It’s actually not correct that MongoDB is not ACID-compliant. On the contrary, MongoDB is ACID-compilant at the document level. Any update to a single document is Atomic: it either fully completes or it does not Consistent: no reader will see a “partially applied” update Isolated: again, no reader will see a “dirty” read Durable: (with … Read more
Add primary key to existing table
drop constraint and recreate it alter table Persion drop CONSTRAINT <constraint_name> alter table Persion add primary key (persionId,Pname,PMID) edit: you can find the constraint name by using the query below: select OBJECT_NAME(OBJECT_ID) AS NameofConstraint FROM sys.objects where OBJECT_NAME(parent_object_id)=’Persion’ and type_desc LIKE ‘%CONSTRAINT’
postgresql – add boolean column to table set default
ALTER TABLE users ADD COLUMN “priv_user” BOOLEAN DEFAULT FALSE; you can also directly specify NOT NULL ALTER TABLE users ADD COLUMN “priv_user” BOOLEAN NOT NULL DEFAULT FALSE; UPDATE: following is only true for versions before postgresql 11. As Craig mentioned on filled tables it is more efficient to split it into steps: ALTER TABLE users … Read more
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
Taken from Using FOREIGN KEY Constraints Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. It will reject any INSERT or UPDATE operation that attempts to create a foreign … Read more
Use email address as primary key?
String comparison is slower than int comparison. However, this does not matter if you simply retrieve a user from the database using the e-mail address. It does matter if you have complex queries with multiple joins. If you store information about users in multiple tables, the foreign keys to the users table will be the … Read more
Simple way to calculate median with MySQL
In MariaDB / MySQL: SELECT AVG(dd.val) as median_val FROM ( SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum FROM data d, (SELECT @rownum:=0) r WHERE d.val is NOT NULL — put some where clause here ORDER BY d.val ) as dd WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) ); Steve Cohen points out, that after the first pass, … Read more
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
What is a LATERAL join? The feature was introduced with PostgreSQL 9.3. The manual: Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) Table functions appearing … Read more