What is the best way to write a simple sql statement that groups on one column and uses the first value located on the other columns?
ex:
table users: id, email, name, password
table posts: user_id, title, body
SELECT users.*, COUNT(users.id) as post_count
FROM users, posts
WHERE users.id = posts.user_id
GROUP BY users.id
ORDER BY post_count
This will not work as you must include each user column in an aggregate function or in the group by clause. My actual table has many fields and I don't want to specify each column.
People succeed in answering Rich Collins's questions 37% of the time (32 successes in 86 attempts).
Answers by: quag | DarcyB
select users.*, post_count from
(SELECT user_id, COUNT(user_id) as post_count
FROM posts
GROUP BY users.id
ORDER BY post_count) a, users
where user_id = users.id
ORDER BY post_count
or a join could be used
Hmm this is the actual SQL statement:
SELECT users.*, sub_select.solutions as solutions
FROM users, (
SELECT COUNT(user_id)
FROM conversations
WHERE conversations.success = true
GROUP BY user_id
) sub_select
WHERE sub_select.user_id = users.id
ORDER BY solutions
I am getting this error:
column sub_select.solutions does not exist
Ah nevermind I had a typo. You are both right :S
SELECT users.*, sub_select.solutions as solutions
FROM users, (
SELECT COUNT(user_id) as solutions
FROM conversations
WHERE conversations.success = true
GROUP BY user_id
) sub_select
WHERE sub_select.user_id = users.id
ORDER BY sub_select.solutions
Solutions need to be included in the sub_select. If the Count(user_id) is the solutions, then add an "as solutions" (as above).
If solutions is another field, then change the select to be SELECT COUNT(user_id), solutions