7 ms·

> How long would it take you to write the SQL for that? select distinct on (album_id) * from tracks order by album_id, milliseconds desc;

dewey 3y ago
With the additional benefit that resources about SQL are everywhere and every question has been answered already multiple times. On top of that you are most likely to encounter SQL at a job than PRQL.
- deleted 3y ago
  [deleted]
hobs 3y ago
You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.
- hans_castorp 3y ago
  The question was "the longest track per album", so I'd expect one row per album as the result (which is what my query does). But adding a LIMIT would not do that. It might be that the question was "the longest track across all albums" which indeed would require a LIMIT
  - deleted 3y ago
    [deleted]
da_chicken 3y ago
Eh, `DISTICT ON` is a custom PostgreSQL extension. A standard* method would be: SELECT * FROM tracks QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1; But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using: SELECT * FROM ( SELECT * ,row_number() over (partition by album_id order by milliseconds desc) rn FROM tracks ) x WHERE x.rn = 1; That said, I still don't think PRQL is particularly amazing. I can't tell if it's merely syntactic sugar for SQL, or if it's actually meant to control query execution. If it's the former, it's likely to frustrate developers because it's actually just another layer of abstraction. If it's the latter, then it requires the developer to not only understand the data model well enough to be able to write SQL queries, they need to be able to understand the RDBMS impementation details well enough to be able to write queries that best take advantage of the current database's indexes, statistics, and configuration. Even something as simple as sorting before filtering or projecting can be a significant performance issue. Nevermind the fact that relational algebra done in the wrong order can be non-deterministic or not equivalent transformations, so even if the query processor is smart enough to do rewrites whatever the developer enters might be logically different unintentionally. Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown. *: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.
- 6gvONxR4sf7o 3y ago
  Damn. Reading your comment, i was about to be really glad that this pain would be a thing of the past before too long. Too bad it didn't make the standard :(
  - da_chicken 3y ago
    Well, even when it's part of the standard it will take about 6 years before your vendor chooses to implment it. And even then, it'll be another 6 years before your application vendor finally upgrades to it. And even then, it'll be another 6 years before the database feature is allowed to be enabled. And even then, your reporting software won't support it.
    - reactivenz 3y ago
      Snowflake has had it for years, and even then it is trivial to write as a nested SELECT. The bigger problem, is the "over use of tools to boost developers" the real problem, is putting poor developers into the pipeline. complex analytics is SQL is just simple, and lovely. The fact other struggle is not going to be helped by pretending the "code" looks more C like, they need to learn to think like a performant machine, and then be productive.
- hans_castorp 3y ago
  The PRQL extension discussed here is for Postgres, so my solution was Postgres specific. Since when is QUALIFY part of the SQL standard? So far I have only seen it as a proprietary feature in Terradata.

dewey 3y ago

With the additional benefit that resources about SQL are everywhere and every question has been answered already multiple times. On top of that you are most likely to encounter SQL at a job than PRQL.

deleted 3y ago
[deleted]

hobs 3y ago

You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.

hans_castorp 3y ago
The question was "the longest track per album", so I'd expect one row per album as the result (which is what my query does). But adding a LIMIT would not do that. It might be that the question was "the longest track across all albums" which indeed would require a LIMIT
- deleted 3y ago
  [deleted]

da_chicken 3y ago

Eh, `DISTICT ON` is a custom PostgreSQL extension. A standard* method would be: SELECT * FROM tracks QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1; But the QUALIFY clause is so new that it doesn't work on most RDBMSs. If you're on MS SQL Server, you're still using: SELECT * FROM ( SELECT * ,row_number() over (partition by album_id order by milliseconds desc) rn FROM tracks ) x WHERE x.rn = 1; That said, I still don't think PRQL is particularly amazing. I can't tell if it's merely syntactic sugar for SQL, or if it's actually meant to control query execution. If it's the former, it's likely to frustrate developers because it's actually just another layer of abstraction. If it's the latter, then it requires the developer to not only understand the data model well enough to be able to write SQL queries, they need to be able to understand the RDBMS impementation details well enough to be able to write queries that best take advantage of the current database's indexes, statistics, and configuration. Even something as simple as sorting before filtering or projecting can be a significant performance issue. Nevermind the fact that relational algebra done in the wrong order can be non-deterministic or not equivalent transformations, so even if the query processor is smart enough to do rewrites whatever the developer enters might be logically different unintentionally. Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown. *: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.

6gvONxR4sf7o 3y ago
Damn. Reading your comment, i was about to be really glad that this pain would be a thing of the past before too long. Too bad it didn't make the standard :(
- da_chicken 3y ago
  Well, even when it's part of the standard it will take about 6 years before your vendor chooses to implment it. And even then, it'll be another 6 years before your application vendor finally upgrades to it. And even then, it'll be another 6 years before the database feature is allowed to be enabled. And even then, your reporting software won't support it.
  - reactivenz 3y ago
    Snowflake has had it for years, and even then it is trivial to write as a nested SELECT. The bigger problem, is the "over use of tools to boost developers" the real problem, is putting poor developers into the pipeline. complex analytics is SQL is just simple, and lovely. The fact other struggle is not going to be helped by pretending the "code" looks more C like, they need to learn to think like a performant machine, and then be productive.
hans_castorp 3y ago
The PRQL extension discussed here is for Postgres, so my solution was Postgres specific. Since when is QUALIFY part of the SQL standard? So far I have only seen it as a proprietary feature in Terradata.