|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 7th, 2003, 04:38 PM
|
|||
|
|||
|
Query Help - Max, Group by
I am trying to write a what seems to be a simple query but am stuck. any help will be greatly appriciated.
the problem is i have a single table having 4 fields 1. - ID 2. - User 3. - Product Name 4. - Quantity what i want is to display the max Product ordered by each user so if the table looks like this ID User Product Quantity 1 u1 p1 1 2 u1 p2 2 3 u1 p3 3 4 u2 p3 1 5 u2 p4 2 6 u2 p5 3 i want my results to look like u1 p3 u2 p5 display the user and product that is ordered most by each user thanks once again for helping me out. mnwt
|
|
#2
December 7th, 2003, 04:53 PM
|
|||
|
|||
|
Code:
select * from t as q where quantity = (select max(quantity) from t where t.productName = q.productName)
|
|
#4
December 7th, 2003, 06:46 PM
|
|||
|
|||
|
Code:
SELECT u1.username,
u1.product,
u1.quantity
FROM userprod u1
WHERE u1.quantity = (SELECT MAX (quantity) FROM userprod u2 WHERE u2.username = u1.username)
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Query Help - Max, Group by |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
