File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes MySql query structure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySql query structure" Watch "MySql query structure" New topic

MySql query structure

Isaac Ferguson
Ranch Hand

Joined: Jun 22, 2012
Posts: 751

could any member tell me how could I figure out the next query.

In my DDBB I have a field "numbers of times the user has used the system", country, username, userId and timesAccessed(sessionId). I am tying to figure out the next:

Details on the 10 users who more times have accessed to the system, split out by country.

For example:

Select username, userId from Users ORDER BY MAX(count(sessionId)) DESC LIMIT 10 where country="US";

Do you think this query makes any sense?

Many Thanks,
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

There where clause should be before the order by. Also, if you want to split by country rather than just the one, look at group by or a subquery.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Wendy Gibbons

Joined: Oct 21, 2008
Posts: 1107

do you want the top 10 users in each country?
or you want to know the country of the top 10 users?
I agree. Here's the link:
subject: MySql query structure
It's not a secret anymore!