Choosing SQL with the By group and Order By Date group

Question

I am using SQL Server 2008, and I am wondering if I can execute my query in a single select clause and without a subquery.

I want to set the variable to true if the field in the record is true in the last 10 records created, where if the field is true in the last 10 records, the variable will be true, and if it is false, the variable will be false, also if the total number of records is less 10, then the variable will also be false.

My problem is to get the last 10 created records, then I need to specify the user order in descending order and make a filter in the top ten, so my query should look like this, where is this invalid query:

declare @MyVar bit set @MyVar = 0 select top(10) @MyVar = 1 from MyTable where SomeId = 1000 and SomeFlag = 1 group by SomeId having count(SomeId) >= 10 order by CreatedDate 

Please provide me your suggestions.

Here is an example, let's say we have the following table and say that I want to check the last 3 entries for each id:

 ID Joined CreatedDate 1 true 03/27/2013 1 false 03/26/2013 1 false 03/25/2013 1 true 03/24/2013 1 true 03/23/2013 2 true 03/22/2013 2 true 03/21/2013 2 true 03/20/2013 2 false 03/19/2013 3 true 03/18/2013 3 true 03/17/2013 

For id = "1", the result will be FALSE, since the last 3 entries created do not have true for the JOINED field in these three entries.

If id = "2", the result will be TRUE, since the last 3 records created have a true JOINED field in these three records.

If id = "3", the result will be FALSE, since the most recently created records must be at least 3 records.

Answer 1

(The answer is given before the OP indicated in 2008. The following works only in 2012)

---

This query gives (for each ID value) the number of rows in the last 10 for which the flag is 1. It should be simple enough (if required) to filter it only for rows for which the counter is 10 and limit it to one ID value .

Without the best sample data, I will leave this for now:

 ;with Vals as ( select *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreatedDate DESC) as rn, SUM(CASE WHEN Flag = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY CreatedDate ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as Cnt from T1 ) select * from Vals where rn = 1 

(It depends on the version of SQL Server 2012 OVER clause - but you did not specify which version)

Result:

 ID Flag CreatedDate rn Cnt ----------- ----- ----------------------- -------------------- ----------- 1 1 2012-01-12 00:00:00.000 1 10 2 1 2012-01-12 00:00:00.000 1 9 3 1 2012-01-12 00:00:00.000 1 6 

(Only ID 1 matches your criteria)

Sample data:

 create table T1 (ID int not null,Flag bit not null,CreatedDate datetime not null) insert into T1 (ID,Flag,CreatedDate) values (1,1,'20120101'), (1,0,'20120102'), (1,1,'20120103'), (1,1,'20120104'), (1,1,'20120105'), (1,1,'20120106'), (1,1,'20120107'), (1,1,'20120108'), (1,1,'20120109'), (1,1,'20120110'), (1,1,'20120111'), (1,1,'20120112'), (2,1,'20120101'), (2,1,'20120102'), (2,1,'20120103'), (2,1,'20120104'), (2,1,'20120105'), (2,1,'20120106'), (2,0,'20120107'), (2,1,'20120108'), (2,1,'20120109'), (2,1,'20120110'), (2,1,'20120111'), (2,1,'20120112'), (3,1,'20120107'), (3,1,'20120108'), (3,1,'20120109'), (3,1,'20120110'), (3,1,'20120111'), (3,1,'20120112') 

Answer 2

In SQLServer2008, instead of a subquery, you can use CTE with the ROW_NUMBER () ranking function

  ;WITH cte AS ( SELECT ID, CAST(Joined AS int) AS Flag, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CreatedDate DESC) AS rn FROM dbo.test63 t ) SELECT ID, CASE WHEN SUM(Flag) != 3 THEN 0 ELSE 1 END AS Flag FROM cte WHERE rn <= 3 GROUP BY ID 

SQLFiddle Demo