Last rows ..... - SQL Server Forums

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)

Last rows .....

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-12-30 : 09:19:12

hi

Is it possible to poll last 3rows (those match with specific condition,eg. id=4) and should return 1 or 2 rows if there's no 3 rows for such condition ?

Since i'm newbie , I cant check it out. I only know how to get last column of last row.

Pls help me !
thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-30 : 09:20:54

You are not clear. Can you post sample data and expected output ?

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-12-30 : 09:27:12

Let's say..

mytable
id col1 col2
1 aa 123
1 bb 324
1 cc 343
1 dd 544
2 ee---------
2 ff---------
for id=1 , should return last 3 rows bb,cc and dd
for id=2, should return both ee and ff coz there's only 2rows.

thanks for ur help.

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 09:44:11

If I understand you correctly, try this:

declare @local_id INT
set @local_id = x -- some value
select top 3 * from mytable where id = @local_id

This will return up to 3 rows per whatever id valued you pass in, which is what you want yes?

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 09:45:48

one thing go keep in mind : the actual rows returned is indeterminate unless you order the recrods with an ORDER BY in the select. So in theory, if you don't have an ORDER BY, you could return any 3 rows per set.

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-12-30 : 09:56:44

Thanks alot for ur reply !

actually i want last 3 rows , not top 3 rows
ur query will retrun>> a,b,c
but it should retrun>b,c,d

quote:
Originally posted by MichaelHLutz

If I understand you correctly, try this:

declare @local_id INT
set @local_id = x -- some value
select top 3 * from mytable where id = @local_id

This will return up to 3 rows per whatever id valued you pass in, which is what you want yes?

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 10:49:46

then use ORDER BY to force the last 3 rows that you want to be the first 3, then you'll get the set you need.

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 11:02:55

btw you can sort descending via the following :

ORDER BY col1 DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:33:26

you specified last 3 rows, but didnt specify based on what order. select queries never guarantee you order of retrieval of values unless you specify order explicitly by means of ORDER BY. so you need to first decide on what order you want to retrieve.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:35:10

or you can just do like this


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) AS Seq,*
FROM Table
)t
WHERE t.id=@ID
AND t.Seq<=3

WHERE

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 12:36:44

that's very slick, but probably less efficient.

One nice attribute of TOP is that it is a non-blocking interator and it stops processing after the required rows are produced. In the case of using range functions the value of every rows is probably calculated first, then the where clause is applied. The sort has to take place in either case.

Nice solution though, I like use of the new SQL 2005 features!

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 12:39:03

one more quick thought ... the benefit of the ROW_NUMBER() OVER solution is that you could return the last 3 rows per group instead of only for 1 particular group, which is probably what the person asking wants. So that's good - it would be much faster doing this than executing a TOP query for each id in a loop!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:43:28

quote:
Originally posted by MichaelHLutz

one more quick thought ... the benefit of the ROW_NUMBER() OVER solution is that you could return the last 3 rows per group instead of only for 1 particular group, which is probably what the person asking wants. So that's good - it would be much faster doing this than executing a TOP query for each id in a loop!

yeah, thats why i thought of providing that solution after my initial query

MichaelHLutz
Starting Member

19 Posts

Posted - 2008-12-30 : 12:46:19

good thinking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:48:12

thanks