Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: complicated select statement

    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?

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Tell as which result you want to get ?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by benams View Post
    I have to get the top 2 read contents for the last 2 days.
    please explain "last 2 days"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2010
    Posts
    3

    The result I want

    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... )

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •