Thread: show top values
View Single Post
  #1  
Old December 30th, 2009, 12:40 AM posted to microsoft.public.access.queries
Ian
external usenet poster
 
Posts: 485
Default show top values

posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000