Query get last 3 records per ID & category wise without using Row_number()

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a History Table tbl_history which contains crores of Records.

Table Sample Data:-

id 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.

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

Please help me out.

Regards,
Girish

Posted 23-Feb-12 3:31am

GirishChhatani1

Updated 23-Feb-12 3:34am

v2

Add a Solution

Comments

Herman<T>.Instance 23-Feb-12 9:43am

how many rows data and how are indexes setup on that table?

GirishChhatani1 23-Feb-12 9:49am

Table have around 1 crore of data and Clustered index is created on ID Column.

ZurdoDev 23-Feb-12 10:15am

It is likely slow because of indexing. I use Row_Number all the time. You'll have to group and use nested queries with ORDER BY DESC and top 3.

GirishChhatani1 23-Feb-12 10:33am

i tried but still it doesn't work.

select top 3 ID,Volume,MAX(DeletedTime) as MaxDeletedTime from tbl_History with(nolock)
group by ID,Volume
order by MAX(DeletedTime) desc

Ganesan Senthilvel 23-Feb-12 11:26am

SELECT column1,column2 ,...,columnn FROM

(SELECT rownum a, column1,column2 ,...,columnnFROM table_name)

WHERE a > ( SELECT (max(rownum)-N)FROM table_name)

GirishChhatani1 24-Feb-12 10:07am

But rownum is not available in MS Sql Server.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shameel · Answer 1 · 2012-02-26T23:15:00

Solution 1

If the query works and you think it is slow, try indexing the columns that are being queried and joined. ROW_NUMBER is not slow, it is how you use it that makes it slow.

Posted 26-Feb-12 23:15pm

Shameel

Comments

GirishChhatani1 27-Feb-12 5:39am

Hello Shameel,
I have created Clustered index on id column.There are 1 Crore records in a table.

[no name] 27-Feb-12 6:02am

Try creating indexes on the other two columns as well. Alternatively include the volume column in the id column index using the INCLUDE clause.