Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

RE: SQL Finding the most frequent occurance of a value (mode)

843854Apr 30 2005 — edited Jun 1 2006

Hi,

I have a column in a table which contains values, I would like to find out which value occurs the most often. I thought I had seen a similar post in this forum, but can not find it anywhere. I have looked on the internet and have found this query, but it does not work for me.

Query: "SELECT TOP 1 myColumn FROM (SELECT myColumn COUNT(*) cnt FROM myTable GROUP BY myColumn) DERIVEDTBL ORDER BY cnt DESC"

I have spent ages trying to get this to work, with no luck.

I get this exception "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (m
issing operator) in query expression 'COUNT(*) cnt'."

Any help would be greatley appreciated, whether it's an answer or a reference to a source that is more appropriate. Any links to decent SQL forums would be good, I have had trouble finding any.

Thanks

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 29 2006

Added on Apr 30 2005

#java-database-connectivity-jdbc

17 comments

880 views