> Would you elaborate on why you consider these types unfit for sorting?

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.)

Gotcha. Thanks for clarifying. You're right that you're effectively encoding the position, and that's part of the trade off. Edit to add: As for knowing the position by looking at it, I'd argue that most use cases, one wants to know the positions relative to others, which means you want to know them in context, next to the other rows. If you want to know the absolute numbers, you can use rank or row_number to add it: perhaps even provide a view if this is something you do frequently.

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.