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 ss.count, u.* from users u, (SELECT count(users_id), users_id from posts group by users_id) ss WHERE ss.id = u.id order by 1
Opps typo;
select ss.post_count, u.* from users u, (SELECT count(users_id) as post_count, users_id from posts group by users_id) ss WHERE ss.users_id = u.id order by 1
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