Articles Cookbook
26 Aug 2014
Patrycja Dybka

SQL vs. Mongo query

In recent times, NoSQL databases have become a hot topic and have gained a crowd of advocates. Indeed, when NoSQL database started to arise, SQL-to-NoSQL converters arose with them as well. But unfortunately, thanks to the code conversion possibility, SQL supporters gained unquestionable evidence that in many cases the good, old SQL is much easier to use. Especially when it comes to queries. I performed a quick Google search and found plenty of nice converter tools.
Read more
22 May 2014
Agnieszka Kozubek-Krycuń

DELETE RETURNING clause in PostgreSQL

The standard DELETE statement in SQL returns the number of deleted rows. DELETE FROM external_data; DELETE 10 In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted. DELETE FROM external_data RETURNING *; id | creation_date | user_id | data ----+---------------------------+---------+---------------- 101 | 2014-05-06 13:10:45.09484 | 23 | 'Some text' 102 | 2014-06-10 22:23:12.12045 | 25 | 'Some other text' (2 rows) DELETE 2
Read more
21 May 2014
Agnieszka Kozubek-Krycuń

How to Select the First Row in a Group?

Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON. Let's say I want to select one weather report for each location. location time report Ottawa 2014-05-15 8:00 sunny 2014-05-15 11:00 cloudy 2014-05-15 15:00 rainy Warsaw 2014-05-15 8:00 overcast 2014-05-15 11:00 sunny 2014-05-15 15:00 rainy SELECT DISTINCT ON (location) location, time, report FROM weather_reports;
Read more
7 Jan 2014
Agnieszka Kozubek-Krycuń

MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names. if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} parent_name child_name John Tom Michael Sylvie John Anna Michael Sophie To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows:
Read more