SELECT ONLY TOP NO. OF RECORDS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT ONLY TOP NO. OF RECORDS

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-08 : 21:40:23

/*
Hey guys, i need help to get record of TOP 3 in DESC order of DATE_CHARGED for each ACCOUNT NO and
add RECORD_NO(autonumber) field in sequence of numbers starting from zero.
Autonumber is also per ACCOUNT NO. I'm using SQL 2000

The output below is the result that i wanted to produce. And please take a look at my query below
maybe you could suggest additional queries so that i can derive the result.
*/

declare @sample table (account_no int, date_charged smalldatetime)

insert into @sample
select 1, '06/15/2006'
union all select 1, '09/15/2006'
union all select 1, '12/15/2006'
union all select 1, '1/15/2007'
union all select 1, '4/15/2007'
union all select 1, '5/15/2007'
union all select 1, '8/15/2007'
union all select 2, '11/25/2006'
union all select 2, '12/25/2006'
union all select 2, '1/25/2007'
union all select 2, '2/25/2007'
union all select 2, '6/25/2007'
union all select 2, '7/25/2007'
union all select 2, '9/25/2007'
union all select 4, '4/7/2006'
union all select 4, '5/7/2006'
union all select 4, '6/7/2006'
union all select 4, '7/7/2006'


/* 
--this is the expected output
account_no	date_charged		record_no
1		2007-04-15 00:00:00	0
1		2007-05-15 00:00:00	1
1		2007-08-15 00:00:00	2
2		2007-06-25 00:00:00	0
2		2007-07-25 00:00:00	1
2		2007-09-25 00:00:00	2
4		2006-05-07 00:00:00	0
4		2006-06-07 00:00:00	1
4		2006-07-07 00:00:00	2


I used a query to put a record number for each ACCOUNT_NO but the problem is it takes 12 seconds to 
put the autonumber field for 294,694 records. THIS QUERY IS VERY SLOW. :) 

This is the query that i used:
	
select account_no, [date] as date_charged, (select count(*) from tblcharges where [date] <= c.[date] and account_no = c.account_no) from tblcharges  c
order by account_no,[date]

tblCharges contains almost 300 thousand records so using the above query takes more than 12 seconds using 
pentium 4, 256 MB mem.
*/

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-08 : 22:07:28

[code]SELECT *
FROM
(
SELECT s.account_no, s.date_charged,
rec_no = row_number() OVER (PARTITION BY account_no ORDER BY date_charged DESC) - 1
FROM @sample s
) a
WHERE rec_no <= 2[/code]

KH
[spoiler]Time is always against us[/spoiler]

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-08 : 22:08:49

An error has occured

Server: Msg 195, Level 15, State 10, Line 28
'row_number' is not a recognized function name.

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-08 : 22:09:34

are you using SQL Server 2005 or 2000 ?

KH
[spoiler]Time is always against us[/spoiler]

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-07-08 : 22:45:04

I'm using SQL 2000, i forgot that you already have a different forum for SQL 2000

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-08 : 22:56:05

{MOD : Please move to SQL 2000 Forum}

As you have a large number of records in the table, it is best you do the rec_no in your front end. Any solution in SQL will not give you best performance.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-09 : 04:13:13

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

DaleJ
Starting Member

7 Posts

Posted - 2007-07-09 : 04:24:06

The first part of this will give you the groups of 3.
The second assigns the record nbr. This part can be done in your UI or middle layer if needed, instead of the SS.

select s.*
into #temp
from @sample s
inner join
(
select distinct account_no
from @sample
) as list
on s.account_no = list.account_no
and s.date_charged in
( select top 3 t.date_charged from @sample t
where t.account_no = s.account_no order by date_charged desc
)
order by s.account_no, s.date_charged

select *,
(select count(*) from #temp t2
where t2.account_no = t1.account_no and t2.date_charged < t1.date_charged) as record_no
from #temp t1
order by t1.account_no, t1.record_no

senpoly
Starting Member

19 Posts

Posted - 2007-07-10 : 05:45:40

if u are using sql 2005, this could be made easy by using cross apply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources