Comment by mamcx
7 years ago
> What do you mean with group by/ ROLLUP
ROLLUP is not the same than groups. With
[1:a 1:b 2:a]
Group by (as is normaly in other langs):
[1:[a,b] 2:[a]]
Sql "group by" instead is for summary/tally of data:
[1:2 2:1] //assuming count
ROLLUP is for make a nice report!
[1:[a,b] Count:2; 2:[a] Count:1]
What SQL lacks (in this case), but the relational model not, is the ability to nest relations as easily as JSON.
----
Part of the deceptive power of SQL is that is very adequate and for "just query" of data is ok for most cases.
But as "programming language" it lacks some extra power and versatility.
> What SQL lacks (in this case), but the relational model not, is the ability to nest relations as easily as JSON.
SQL has nested relations since SQL:2003[1] (called multisets). They are not widley supported and frankly speaking I don't like them.
JSON was finally added with SQL:2016[2].
[1] https://sigmodrecord.org/publications/sigmodRecord/0403/E.Ji...
[2] https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js...
This feels like a misunderstanding of SQL. It is not a programming language. It is a data selection language.
And group by does work the same as other languages. However, you can't talk of the group by without the select. So, if you use count, or sum, or max, or ..., Yes it will be a summary. You can use string_agg or friends if you want all values. But... Probably safer to get each as a row.
"We Can Do Better Than SQL"
SQL is a programming language. Is just too limited and that is why many can't imagine to use it for a full project.
But that limitations have no sense. The amount of code, time and efficiency that bring is huge (I know, I code in that kind of lang before) and is discussed in:
http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
In special look a point 5/6 of Summary. The irony is that we already have a "solution"(sql) to the whole problem, but is too limited and awkward to be used in practique.
I'm not sure why, but this post was hard for me to follow. The linked article is borderline incoherent to me.
What gets me, is even in languages where I'm not working with a relational database, the API for selecting data almost always drifts to something akin to SQL. To the point that I cringe if someone tries to force a new API.