sql - Query to ORDER BY the number of rows returned from another SELECT

Question

I'm trying to wrap my head around SQL and I need some help figuring out how to do the following query in PostgreSQL 9.3.

I have a users table, and a friends table that lists user IDs and the user IDs of friends in multiple rows.

I would like to query the user table, and ORDER BY the number of mutual friends in common to a user ID.

So, the friends table would look like:

user_id | friend_user_id
1       | 4
1       | 5
2       | 10
3       | 7

And so on, so user 1 lists 4 and 5 as friends, and user 2 lists 10 as a friend, so I want to sort by the highest count of user 1 in friend_user_id for the result of user_id in the select.

Answer 1

The Postgres way to do this:

SELECT *
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS friends
   FROM   friends
   ) f USING (user_id)
ORDER  BY f.friends DESC NULLS LAST, user_id  -- as tiebreaker

• The keyword AS is just noise for table aliases. But don't omit it from column aliases. The manual on "Omitting the AS Key Word":

  In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

  Bold emphasis mine.

• ISNULL() is a custom extension of MySQL or SQL Server. Postgres uses the SQL-standard function COALESCE(). But you don't need either here. Use the NULLS LAST clause instead, which is faster and cleaner. See:

  • PostgreSQL sort by datetime asc, null first?

• Multiple users will have the same number of friends. These peers would be sorted arbitrarily. Repeated execution might yield different sort order, which is typically not desirable. Add more expressions to ORDER BY as tiebreaker. Ultimately, the primary key resolves any remaining ambiguity.

• If the two tables share the same column name user_id (like they should) you can use the syntax shortcut USING in the join clause. Another standard SQL feature. Welcome side effect: user_id is only listed once in the output for SELECT *, as opposed to when joining with ON. Many clients wouldn't even accept duplicate column names in the output.