469,927 Members | 2,013 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

getting top count across multiple fields

although i don't think this is do-able using SQL, i would ask and maybe
an expert can help me out. assum i have the following table:

Date Code Count
2006-06-06 abc 1234
2006-06-06 abc 14
2006-06-06 abc 87
2006-06-06 xyz 1353
2006-06-06 xyz 19
2006-06-06 xyz 86
2006-06-06 xyz 176
2006-06-06 xyz 43
2006-06-06 ppp 67
2006-06-06 ppp 45
2006-06-06 ppp 200

what i want is the top 2 Count from each Code for Date like this:

Date Code Count
2006-06-06 abc 1234
2006-06-06 abc 87
2006-06-06 xyz 1353
2006-06-06 xyz 176
2006-06-06 ppp 200
2006-06-06 ppp 67

obviously the following doesn't work:

select Date,Code,Count from myTable group by Date,Code,Count order by
Count desc limit 2

because the order and the limit will be across the whole table not code
by code so you end up with only 2 rows with count 1353 and 1234.

any idea if this is do-able using SQL only?

Jun 11 '06 #1
0 1518

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bill | last post: by
1 post views Thread by Chris Wolfe | last post: by
3 posts views Thread by Zb Bornemann | last post: by
2 posts views Thread by cefrancke | last post: by
1 post views Thread by griemer | last post: by
1 post views Thread by sunilkeswani | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.