5 ms·

It would be nice to see an example for this kind of query. BTW, SQL seems like a terrible language. For example, why would it need to enforce ordering for keyw

• MichaelBurge 10y ago
  One reason is to remind you of the SQL order-of-operations: * The from clause retrieves records from data sources like set functions, tables, or views. * The on/using clause joins tables * The where clause filters out records * The group by clause assigns records to buckets * The having clause filters out records * The select clause runs * The order by sorts * The limit clause picks records In this query: > select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true order by 1 limit 1; The only out-of-place component is the select and window function(which runs after the group by clause). In a more complex query with multiple selects in scope, the order of terms helps remind you which parts are running in which order. Note that the optimizer won't strictly enforce the order; it's just a guide to interpret what the results should be. If you need to do something out-of-order, you can use a subselect: > select * from (select sum(1) over () from (select 1 a) x join (select 1 a) y using (a) where 1 = 1 group by true having true limit 1 ) x order by 1; Here, I've swapped the limit clause and the order-by clause, although you can still see the order of operations in the explain plan being separated by a 'Subquery scan'.
  • kbenson 10y ago
    > The having clause filters out records I think you mean "The having clause filters out buckets", right?
• return0 10y ago
  > SQL seems like a terrible language. The reason you give is too trivial to justify calling it "a terrible language"
  • d33 10y ago
    Okay, so another reasons are: that 1) it's terribly difficult to parse and 2) on large tables, messing with query planner is actually more work than just specifying the algorithm you'd like to use. Also: 3) yes, there is an SQL standard, but generally if you're writing any application that uses SQL, you're going to end up writing code for a specific database or use an ORM.
  • JustUhThought 10y ago
    E.F. Codd (author of the whitepaper that is the foundation of all RDBMSs, for which SQL was created to query) was of the opinion that the SQL was a terrible language. His reasoning for saying so are very clearly and well speelled out in several books and articles of his.
  • mamcx 10y ago
    That reason is in fact a good one. The order is "wrong" from the point of view of a developer and maybe ok from the POV of the original intent of SQL: The end-user was supposed to run SQL against databases. Few ad-hoc queries, even complicated, can be managed. But using SQL all the time, and the language is clearly the wrong thing to talk to the DB (is like JS: Is what you have instead of what you want). Also: The fact that everyone run (even if eventually fail) for a ORM instead of SQL, or the way people imagine NOSQL is "better" -is very telling is NO SQL- show this. The relational model is so simple and powerfull, but SQL is not a good seller of the idea.
    • bluejekyll 10y ago
      Is there another language agnostic option? I suppose it would be interesting if you could use something like the llvm to target a DB's core features, but through your language of choice. I haven't seen anything like that, if it exists. SQL is that standard API today, and ORMs provide the language specific implementation (though sometimes in ways that make it inobvious what the generated SQL will look like).
      • pjungwir 10y ago
• jakobegger 10y ago
  Assume that you have a table 'orders' similar to the following: nr | date | product | quantity ---+------------+---------+---------- 1 | 2016-05-29 | a | 2 2 | 2016-05-29 | a | 7 3 | 2016-05-29 | b | 3 4 | 2016-05-29 | a | 1 5 | 2016-05-30 | b | 5 6 | 2016-05-30 | a | 2 And you want to get daily order totals for each product. Then you do: SELECT date, SUM(quantity) FILTER(WHERE product='a') AS product_a, SUM(quantity) FILTER(WHERE product='b') AS product_b, SUM(quantity) AS total FROM orders GROUP BY date ORDER BY date And the result is date | product_a | product_b | total -----------+-----------+-----------+------- 2016-05-29 | 10 | 3 | 13 2016-05-30 | 3 | 5 | 8
  • snissn 10y ago
    Thank you.
• snaky 10y ago
  > SQL seems like a terrible language That's because idea of SQL was the same as COBOL - kinda-natural more or less ad-hoc end-user-oriented language. Codd ('Relational Completeness of Data Base Sublanguages') - “Clearly, the majority of users should not have to learn either the relational calculus or algebra in order to interact with data bases. However, requesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language.”
• ufmace 10y ago
  The only real beef I have with SQL is that some of the aspects of the ordering seem weird. Why does SELECT put the column list at the start instead of the end? For all but the simplest queries, I want to set up my tables and joins and group bys and whatnot and then think about what columns I want from them. Seems kinda cumbersome to start with SELECT * FROM ... and then go back and fill in the columns later. Why does UPDATE put the SET before the WHERE? Feels like an accident waiting to happen. I got into the habit long ago of writing SET x = x WHERE..., writing the correct WHERE statement, then going back and writing correct/valid sets, just to make sure I don't accidentally execute the query too soon and update the whole table.