complicated select
-
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?
-
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?
The dates are from 10 years ago, so how would they be considered for the last 2 days. :confused:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
The dates are from 10 years ago, so how would they be considered for the last 2 days. :confused:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Maybe it's an old book.
-
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?
Use a combination of Row_Number and Partition. You need to create a 2 query select, the first injects the row_number based on the partition of contentid and date, ordered by readscounter. The outer query selects anything with a row_number < 3.
Never underestimate the power of human stupidity RAH
-
Use a combination of Row_Number and Partition. You need to create a 2 query select, the first injects the row_number based on the partition of contentid and date, ordered by readscounter. The outer query selects anything with a row_number < 3.
Never underestimate the power of human stupidity RAH
-
can you add an example code for what you have suggested? I'm afraid I don't completely understand how to implement this.
Then I found a simpler way when I actually worked the query.
SELECT TOP 2
contentID,
RC
FROM
(SELECT
contentID,
SUM(readsCounter) RC
FROM
Book1 AS B
--WHERE ReadDate BETWEEN this AND that
GROUP BY
contentID) D
ORDER BY RC descNote the where clause will be required when the data set is larger
Never underestimate the power of human stupidity RAH