Sql to Find every 15th row in each group, sql server

[techytechy]

Hi guys,

I am Using sql server 2005 and I need to find the values at every 15th date in each group of a table. Below is the Table "Orders". I am grouping on PatientID

PatientID, AdmitDate, treatment
-------------------------------
1, Date1, Treatment1
1, Date2, Treatment2
....
....
1, Date15, Treatment15
1, Date16, Treatment16
...
2, Date19, Treatment19
2, Date1, Treatment1
....
2, Date15, Treatment15
2, Date3, Treatment3
...
-------------------------------
please note that the date column is not always ascending, its ordered randomly like in patientID = 2 in the above.

Thanks

 

[bborissov]

And how do you know which is 15th date?
Check ROW_NUMBER() function in BOL, esp OVER(PARTITION BY ) clause.
Then use it as a derived table and get every 15th row number

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Which field would indicate the order within the group? You can not rely on the order records are inserted into the table.

Anyway, here is the idea

;with cte as (select PatientID, AdmitDate, Treatment, row_number() over (partition by PatientID order by ?(Field to order)) as RowNum from Orders)

select * from cte where RowNum = 15

 

[techytechy]

Thanks for comments.

@bborissov, @markros I got to order by Admitdate in Asc within each group PatientID and get the row at 15th Admitdate.

We havent used the server specific analytical functions or temp tables yet in our queries, we have always been writing old style sql queries that return data in one final select. Would it be more efficient to use analytical functions instead of standard sql?

It would be helpful if anyone could provide the standard sql style query too.

Thanks!

 

[markros]

Using windowing functions for these kind of problems is the simplest solution, IMHO.

You can look at this MSDN thread

http://social.msdn.microsoft.com/Fo...l/thread/8664e2df-67f1-4e36-b37e-7656b26b3ad8

for alternative solutions for this problem. IMHO, row_number() is the simplest and easiest to remember among them.

 

[bborissov]

That is standard query for 2005 and above
Other way is to use a table variable with identity field, get the min and max values and the calculate 15th row.
But using ROW_NUMBER() is much easier.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Adam Haines solution from the mentioned thread:

--2000 solution
-- Adam Haines [URL unfurl="true"]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8664e2df-67f1-4e36-b37e-7656b26b3ad8[/URL]
SELECT t.empid,t.[surname],t.[department],[t].[salary],(MAX([dept_cnt].emp_cnt) - COUNT(*))+1 AS seq
FROM @t t
INNER JOIN @t t2 ON t2.[department] =t.[department] AND [t2].[salary] <= [t].[salary]
INNER JOIN(SELECT [department], COUNT(*) AS emp_cnt FROM @t GROUP BY Department) AS dept_cnt
	ON [dept_cnt].[department] = t.[department]
GROUP BY t.empid,t.[surname], t.[department], t.[salary]
HAVING (MAX([dept_cnt].emp_cnt) - COUNT(*))+1 <=3
ORDER BY t.[department], (MAX([dept_cnt].emp_cnt) - COUNT(*))+1 ASC

Easy to apply for 15 instead of 3. Yet row_number() solution is so much easier.

 

[markros]

Sorry, with the table creation script the above solution:

DECLARE @t table(empid numeric(10), department VARCHAR(25),surname varchar(10),salary MONEY)

insert into @t values(1,'IS','emp1',10)
insert into @t values(4,'IS','emp2',15)
insert into @t values(5,'IS','emp3',20)
insert into @t values(8,'IS','emp4',40)
insert into @t values(7,'IS','emp5',25)
insert into @t values(2,'IS','emp6',13)

insert into @t values(10,'MKTG','emp7',5)
insert into @t values(11,'MKTG','emp8',24)
insert into @t values(12,'MKTG','emp9',35)
insert into @t values(13,'MKTG','emp10',55)
insert into @t values(14,'MKTG','emp11',67)
insert into @t values(15,'MKTG','emp12',20)

--2000 solution
-- Adam Haines [URL unfurl="true"]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8664e2df-67f1-4e36-b37e-7656b26b3ad8[/URL]
SELECT t.empid,t.[surname],t.[department],[t].[salary],(MAX([dept_cnt].emp_cnt) - COUNT(*))+1 AS seq
FROM @t t
INNER JOIN @t t2 ON t2.[department] =t.[department] AND [t2].[salary] <= [t].[salary]
INNER JOIN(SELECT [department], COUNT(*) AS emp_cnt FROM @t GROUP BY Department) AS dept_cnt
	ON [dept_cnt].[department] = t.[department]
GROUP BY t.empid,t.[surname], t.[department], t.[salary]
HAVING (MAX([dept_cnt].emp_cnt) - COUNT(*))+1 <=3
ORDER BY t.[department], (MAX([dept_cnt].emp_cnt) - COUNT(*))+1 ASC

 

[RyanEK]

Hi,

Here's a way you can do it in one statement. This example returns the 2nd row in each group.

declare @temp table (id int, dt datetime, treat char)
insert into @temp values (1, getdate(),   'a')
insert into @temp values (1, getdate()-1, 'b')
insert into @temp values (1, getdate()-2, 'c')
insert into @temp values (2, getdate(),   'd')
insert into @temp values (2, getdate()-1, 'e')
insert into @temp values (2, getdate()-2, 'f')
insert into @temp values (3, getdate(),   'g')
insert into @temp values (3, getdate()-1, 'h')


select id, dt, treat from 
(
  select id, dt, treat,rank() over (partition by id order by dt asc) as my_rank
  from @temp
) as t
where my_rank = 2

Ryan

 

[markros]

Ryan,

Did you miss the new requirement to do it for SQL Server 2000?

 

[RyanEK]

Hey Markos, hmmm the OP states he is using 2005?

 

[markros]

Thanks for comments. @bborissov, @markros I got to order by Admitdate in Asc within each group PatientID and get the row at 15th Admitdate. We havent used the server specific analytical functions or temp tables yet in our queries, we have always been writing old style sql queries that return data in one final select. Would it be more efficient to use analytical functions instead of standard sql?It would be helpful if anyone could provide the standard sql style query too.Thanks!

No, it would not be efficient and it would be much more complex code similar to the one I posted.

 

[markros]

I mean using windowing functions is more efficient than using any other kind of code.

 

[RyanEK]

Using analytical funtions is much cleaner IMO and the OP is using 2005 so it shouldn't be a problem. But using standard sql you could do the following?

declare @temp table (id int, dt datetime, treat char)
insert into @temp values (1, getdate(),   'a')
insert into @temp values (1, getdate()-1, 'b')
insert into @temp values (1, getdate()-2, 'c')
insert into @temp values (2, getdate(),   'd')
insert into @temp values (2, getdate()-1, 'e')
insert into @temp values (2, getdate()-2, 'f')
insert into @temp values (3, getdate(),   'g')
insert into @temp values (3, getdate()-1, 'h')

select distinct t1.id, t2.*
from @temp t1
inner join @temp t2 on t2.id = t1.id 
and t2.dt = (
 select max(t3.dt) 
 from (
   select top 2 dt
   from @temp t4
   where t4.id = t1.id
   order by dt asc
 ) as t3 )

Ryan

 

[techytechy]

Thanks for the responses!
The solution using rank() and partition looks clean. so will go with that. Though the above solution (@RyanEK) for 2000 works fine too.

Thx

 

[katekis]

I need help on rownum() function. I am trying to create a view (see example below) but I get error:
"The OVER SQL construct or statement is not supported."

I can get the query to work fine but how can I use this query or a simiar one to work as a view?

SELECT CustomerID,
acct_nbr, App_Description,
row_number() over (partition by LEFT(CustomerID,12) order by acct_nbr) as Acct_Count
FROM cfk

 

[gmmastros]

The problem is.... the view builder wizard thing you are using does not support the OVER statement. What you should do is...

Using SQL Server Management Studio (or Query Analyzer), open a new window.

Copy/paste this....

Create View dbo.[!]AnyViewName[/!]
As
SELECT CustomerID,
acct_nbr, App_Description,
row_number() over (partition by LEFT(CustomerID,12) order by acct_nbr) as Acct_Count
FROM cfk

Change the part in red to give it a meaning name.

Run this code by pressing F5 on your keyboard.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom