CODEWITHSUNDEEP
mysqlsql
BryanLavinParmenter
BryanLavinParmenter

Reputation: 416

SQL Ordering Results from Other Query

I have two tables:

`users`
`id` INT(11)
`name` VARCHAR(30)

`items`
`id` INT(11)
`name` VARCHAR(30)
`owner` VARCHAR(30)

And here is my problem: I'm trying to order the users table by the number of unique items that they have in the items table.

To get unique number of items:

SELECT * FROM `items` WHERE `owner`='".$ownername."' GROUP BY `name`

Running this query gives me the number of unique items for some $ownername.

What I'm trying to do is order the users table by the number of unique items that they have in the items table, and I'm not sure how I would do that.

Upvotes: 2

Views: 31

Answers (2)

GolezTrol
GolezTrol

Reputation: 116200

You can join, group and sort by count:

SELECT u.name
FROM users u
JOIN items i
WHERE u.owner='".$ownername."' 
GROUP BY u.name
ORDER BY COUNT(i.id) DESC

Or you can sort by the result of a subselect that counts the items per user:

SELECT u.name
FROM users u
WHERE u.owner='".$ownername."' 
ORDER BY
  (SELECT COUNT(*) FROM items i WHERE i.owner = u.name) DESC

The latter you could also write slightly differently if you want to show the value too:

SELECT u.name,
       (SELECT COUNT(*) FROM items i WHERE i.owner = u.name) as item_count
FROM users u
WHERE u.owner='".$ownername."' 
ORDER BY
  item_count DESC

Upvotes: 2

radar
radar

Reputation: 13425

i used left join so that users with no items also show up.

select u.name , count(distinct i.name) as NumberOfItems
FROM users u
LEFT JOIN items i
on u.name = i.owner
order by count(distinct i.name) desc

Upvotes: 0

Related Questions