-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
A summary to help others. My question was, given a table like this:
> userid, course
> john ethics
> john english
> mary english
> mary math
> mary bio
How to get the user that has taken the most courses and another query to
see which course was taken the most.
Three good answers:
Mark Warrick says to normalize the data. I agree, but in this case it isn't
within my control.
Two different SQL queries (you can see how to modify them to answer the
second question):
- From David Hayes:
select top 1 userID, count(course) from
group by userid
order by 2 desc
- From Joe Celko, courtesy of David Shadovitz:
SELECT userid
FROM mytable
GROUP BY userid
HAVING COUNT(*) +1 > ALL (SELECT DISTINCT COUNT(*) FROM mytable GROUP BY
userid)
Joe C's way doesn't seem as flexible as David's (to my inexperienced eyes).
Is TOP not Ansi SQL?
c
-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8ckt - http://irfaiad.virtualave.net/
Comment: PGP Signed for message verification and/or encryption
Comment: KeyID: 0xD68B61E851046CFD
iQA/AwUBOeCaDtaLYehRBGz9EQLxKgCgwGx08O8eFlZAPBRjGmJfu83aP+0AoMp1
HP4b66xlC4nW6tqF7WVeKfVk
=FIms
-----END PGP SIGNATURE-----
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
