nested - SQL Query to find the value which has max number of occurrences in a table without nesting -


i'm working on following schema (bold text stands pk, , ":" stand referenced tables) :

users(username, name, surname);

products(id, name, quantity);

purchases(user:users, product:products, dateandtime, quantitypurchased);

i want find name , surname of user has made max number of purchases.

firstly use nested query find out number of purchases each user , select user purchased >= values:

select name, surname, username users join purchases on username = user group name, surname, username having count(*) >= all(     select count(*)     utenti join acquisti on username = user     group username) 

is there way achieve same without using nested queries?

thank in advance time.

yes there is. sounds homework assignment, seem have put work it. idea order count(*) , take first row. syntax in sql server, sybase, , access is:

select top 1 name, surname, username users u inner join      purchases p      on u.username = p.user group name, surname, username order count(*) desc; 

other databases put limit 1 clause after order by, instead of top 1. , others may have more arcane syntax.


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -