complicated select statement

benams · #1 (**permalink**) 03-04-11, 13:36

I'm not familiar enough with SQL syntax, and I need to make a little complicated SELECT.
I have a table with 3 fields: contentID, date and readsCounter.
I have to get the top 2 read contents for the last 2 days.
For example:

contentID: 0, date: 1-1-2000, readsCounter: 10
contentID: 0, date: 2-1-2000, readsCounter: 80
contentID: 1, date: 1-1-2000, readsCounter: 40
contentID: 1, date: 2-1-2000, readsCounter: 5
contentID: 2, date: 1-1-2000, readsCounter: 20
contentID: 2, date: 2-1-2000, readsCounter: 30

the lines above are 6 records from my db, contents 0 and 2 are the contents that need to be selected(the contents and the reads sum for the last 2 days).
I'm working with MSACCESS DB. someone can help me?

MStef-ZG · #2 (**permalink**) 03-07-11, 02:55

Tell as which result you want to get ?

r937 · #3 (**permalink**) 03-07-11, 06:38

Quote:

Originally Posted by benams

I have to get the top 2 read contents for the last 2 days.

please explain "last 2 days"

benams · #4 (**permalink**) 03-07-11, 08:14

contentID: 0, readsCounter: 90
contentID: 2, readsCounter: 50

as you can see, I want the ID off the most read pages(during the last 2 days) and the sum of the reads

the "last 2 days" int the example are: 1-1-2000 and 2-1-2000 ( in the real program it changes every day... )

r937 · #5 (**permalink**) 03-07-11, 08:28

try this (untested) --

Code:

SELECT TOP 2
       contentID
     , SUM(readsCounter) AS total
  FROM daTable
 WHERE date IN
       ( SELECT TOP 2
                distinct_date
           FROM ( SELECT date AS distinct_date
                    FROM daTable
                  GROUP
                      BY date ) AS d
         ORDER
             BY distinct_date DESC )
GROUP
    BY contentID
ORDER
    BY total DESC

Internet Services

Web Development

Digital Marketing

Consumer Tech