Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

> Say I wanted to present a list of users with their top three to-do items, that's impossible using the above method.

Not really:

    SELECT u.user, i.text
    FROM users u,
    LATERAL (SELECT * FROM items WHERE items.user = u.user ORDER BY items.pos DESC LIMIT 3) i;
(You can also make these columns instead of rows, but as someone already pointed out, you'd usually not do that in SQL.)


You are not wrong, although your example would not work in every SQL dialect (even when rewritten). Sybase's T-SQL does not have equivalent for LIMIT.[0]

My point being, there are some issues with regards to data purity and reasoning, particularly if you want a system where users can build custom reports.

And I just wish the original article highlighted that using floats would have these issues, and if writing SQL like this is important to you, then you might want to reconsider.

(As for your note in brackets, where I work, SQL is basically used as a scripting language. So we would do my example in SQL.)

[0] Yes, I know there is 'set rowcount', but you cannot do that within a sub select.


> Sybase's T-SQL does not have equivalent for LIMIT.[0]

Are you looking for SELECT TOP 3 ...?


First available from 12.5.3. And I cannot be certain we have that version or a newer running.

But you are right, I should have clarified that.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: