Query get last 3 records per ID & category wise

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 2008 Forums

Transact-SQL (2008)

Query get last 3 records per ID & category wise

Author

Topic

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-22 : 08:04:04

Hello Frnds,
I have a History Table which contains crores of Records.
Now what i want is to list out last Records per ID and per Category
without using Row number function the reason i am looking to implement some other logic other than Row_number() because query takes long time to execute.
So pls help me out...

Thanks & Regards,
Girish Chhatani

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-22 : 08:32:16

Something like this but with the given information I can't say if it would work...

select * from table where id > (select max(id) - 3 from table)

No, you're never too old to Yak'n'Roll if you're too young to die.

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-22 : 09:25:29

Thnks for Response.
For more info pls find the required details.
I have a History Table tbl_history which contains crores of Records.
Table Sample Data:-
mcid Volume DeletedTime
1 C 11am
1 C 12am
1 C 1pm
1 C 2pm
1 C 3pm
1 D 11am
1 D 12am
1 D 1pm
1 D 2pm
1 D 3pm
2 C 11am
2 C 12am
2 C 1pm
2 C 2pm
2 C 3pm
2 D 11am
2 D 12am
2 D 1pm
2 D 2pm
2 D 3pm

Now what i want is to list out last 3 Records per ID and per Category i.e in our Case Volume without using Row number function the reason i am looking to implement some other logic other than Row_number() because query takes long time to execute.

Hope this gives u sufficient info.

Thanks & Regards,
Girish Chhatani

sqllearner05
Starting Member

9 Posts

Posted - 2012-02-23 : 01:02:22

@Girish: can u tell me what is the expected result from above data???

sqllearner

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-02-23 : 01:40:23

Are you expecting this one?

Select * from(
Select ROW_NUMBER() over(partition by mc_id,volume order by deleted_time) s_no,mc_id,volume, deleted_time from sql_table)a
where s_no <4

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-23 : 03:12:09

quote:
Originally posted by sqllearner05

@Girish: can u tell me what is the expected result from above data???

sqllearner

Hello Friend,

Expected Output:-

id Volume DeletedTime
1 C 01:00:00 PM
1 C 02:00:00 PM
1 C 03:00:00 PM
1 D 01:00:00 PM
1 D 02:00:00 PM
1 D 03:00:00 PM
2 C 01:00:00 PM
2 C 02:00:00 PM
2 C 03:00:00 PM
2 D 01:00:00 PM
2 D 02:00:00 PM
2 D 03:00:00 PM

Thanks & Regards,
Girish Chhatani

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-23 : 03:14:41

quote:
Originally posted by senthil_nagore

Are you expecting this one?

Select * from(
Select ROW_NUMBER() over(partition by mc_id,volume order by deleted_time) s_no,mc_id,volume, deleted_time from sql_table)a
where s_no <4

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Hello Senthil,
Nope i dont want to make use of Row_number() the reason i am looking for some other logic bcse in the History table there are crores of records so bcse of which query takes long time to execute.

Thanks & Regards,
Girish Chhatani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-23 : 03:24:46

what are the indexes you have for the History Table ?

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

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-23 : 04:36:23

quote:
Originally posted by khtan

what are the indexes you have for the History Table ?

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

Yes Clustered index is Created on mcid column.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-02-24 : 01:49:51

I added a column to the table tbl_history
[id] [int] IDENTITY(1,1) NOT NULL

select C.* from tbl_history A

cross apply (
select top 3 id ,mcid,Volume,deletedTime
from tbl_history B
where 1=1
and A.mcID=B.mcID
and A.volume=B.volume
order by deletedTime desc )C
where A.id=C.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources