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.

you can use IF EXIST and put all your conditions in where — Sachin
– Sachin, Commented Apr 2, 2013 at 7:33
Please reread this sentence, I can't figure it out: "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". It would help if you'd show us sample data and a desired result. — Tim Schmelter
– Tim Schmelter, Commented Apr 2, 2013 at 7:40

Damien_The_Unbeliever · Accepted Answer · 2013-04-02 08:08:20Z

(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')

WOW do i need all that for my query, isn't it somehow complicated?

Oleksandr Fedorenko · Accepted Answer · 2013-04-02 10:41:41Z

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

Collectives™ on Stack Overflow

SQL Select with Group By and Order By Date

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related