SQL Select with Group By and Order By Date

Question

I am using SQL Server 2008, and I am wondering if i can accomplish my query in one select statement and without sub-query.

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

My problem is, to get the latest 10 created records then i need to user order by descending and do the filter on the top 10, so my query should look like the following where it is not a valid 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 with your suggestions.

Here is an example, say we have the following table, and say that i want to check the latest 3 records 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 as the latest 3 created records don't have the value true for JOINED field in those 3 records.

For id="2", the result will be TRUE as the latest 3 created records have true JOINED field in those 3 records.

For id="3", the result will be FALSE as the latest created records to be checked must be minimum 3 records.

Answer 1

In SQLServer2008 instead of subquery you can use CTE with 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

Demo on SQLFiddle

Answer 2

(Answer given before OP specified 2008. The below only works on 2012)

---

This query gives (for each ID value) the number of rows in the last 10 for which flag is equal to 1. It should be simple enough (if required) to filter this to only rows for which the count is 10, and to restrict it to a single ID value.

Without better sample data, I'll leave it at that 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

(This does depend on the SQL Server 2012 version of the OVER clause - but you didn't 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 meets 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')