1. Subquery
SELECT * FROM (
SELECT u.user, i.text, row_number() OVER (PARTITION BY i.user ORDER BY i.pos) AS row
FROM users u INNER JOIN items i USING(user)
) numbered
WHERE row <= 3;
WITH numbered AS (
SELECT u.user, i.text, row_number() OVER (PARTITION BY i.user ORDER BY i.pos) AS row
FROM users u INNER JOIN items i USING(user)
)
SELECT * FROM numbered WHERE row <= 3;
SELECT user, text FROM (
SELECT u.user, text, row_number() OVER (PARTITION BY user ORDER BY rank) as rank
FROM users u
LEFT JOIN items using(user)
) ranked_items
WHERE rank < 4
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;
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.
Are you looking for SELECT TOP 3 ...?
But you are right, I should have clarified that.
Would you elaborate on why you consider these types unfit for sorting? Their orderings are deterministic, and efficiency depends on the implementation of the indexes for those specific types, not inherent in the data types themselves.
> "select u.user, item1=i1.text, item2=i2.text, item3=i3.text"
It would be more natural in SQL to return three rows than return three columns.
SELECT u.user, i.text
FROM users AS u
JOIN items AS i USING (user)
WHERE u.user = ?
ORDER BY i.pos ASC LIMIT 3;
I should have changed that language, what I meant was that they were unfit for reasoning about your data from an isolated point of view, i.e. standing with just one row. To understand an item's position using floats, you must know the entire context.
> It would be more natural in SQL to return three rows than return three columns.
Look at my select again, yours have a specific user in mind, mine doesn't. I want a list of all users who have at least one to-do item. And I want to see their top three to-do items in the report.
Without doing sub-selects in the joins, there is no way to limit this to three when the position is stored as float. And even your sub-selects are going to be complicated.
It might be better to create a temporary table first with each user and their first three items, but that's hardly efficient.
> Why would you prefer to do it the way you describe?
Because I need an overview. And I don't want up to three rows per user, particularly because some users will only get one row, some only two and most three rows. That creates an inconsistent overview.
(And that's assuming you write some SQL to properly limit the joins to three rows.)
As for the report query you describe, you can get at the data you want with windowing functions. There are strategies for performing the pivot as well, but I'd likely do that in application code rather than in the SQL query.
If there's an index on pos, there's no cost to the sort.
Now I want a full select of those 800 users with their first three to-do items in neat columns next to the user.
Your select doesn't do that.
items[0].text + items[1].text + items[2].text
items.slice(0,2).map((e) => e.text).concat('');
Say I wanted to present a list of users with their top three to-do items, that's impossible using the above method.
Them being integers means I can rationally reason about them. I can also infer their position easily. With floats, I don't know where pos=3 is without counting how many have a lower number.Floats only solve two issues: Making inserting easier while still being able to use `order by`. But reasoning about your data becomes a lot harder.