Return bottom 10 rows not working.

Ok so this is more then likely very easy for most. But for me it's hard. I have a stored proc that I've been able to get to retun just the top 10 rows and have been able to order it asending. But it will only return the top 10 and I need to bottom 10 of the selection.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id

How can I make it return to bottom 10. On a side note about this, when it returns the recordset they all have the 'id' of the 'project_id' that I selected. Not that the side note really bothers me as it will not be returning the ID's to be visible.

Any help would be great,
Thanks
Tim

Hi,

To do this, you need to add an ORDER BY clause to your select statement and set it to be DESCENDING.

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
order by project_id desc

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi,

in addition to Geert, be aware that TOP makes no sense without any order clause unless you want to get a set of (possible) random rows out of the database.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ok I've added the order by project_id desc to the statement, but it's still only returning the same 10 rows is ascending order, it'a also changing the ID to the value I queried in this case 12. So it's sorting all of the as 12. I was looking at another and added something and it made it so my results flip around and sort ascending but are returning rows 2-11 not 1-10.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER proc [dbo].[proj_ListFiles](@.project_id int = 0 )
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
or @.project_id = 0
order by project_id desc

Any ideas, what i'm trying to do is limit the results so that way only the last 10 entries of that same project_id will return and not all/the first 10. It will make for easier viewing for me.

By the way thanks for the help.
Tim

|||

Hi,

Since project_id is the same for all the records, you need to sort on a different field then that. For example if you want to have the 10 largest filenames, you perform an ORDER BY length DESC.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Well I figured it out with a little help from a co worker. Not sure if it needs it or not but I just set rowcount to 10 and removed the (@.project_id) from the top 10 command and it works like a dream (a really weird nerdy dream).

ALTER proc proj_ListFiles(@.project_id int)

as

set rowcount 10

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Thanks for the help.

Tim

|||

The procedure mentioned above was wrong, it took the projectId as a static expression leading to the fact that it produced 10 time the number you put in as project id, e.g.

SELECT TOP 10(10) From sys.sysobjects

10
10
10
10
10
10
10
10
10
10

YOu will not have to set SET ROWCOUNT, just use the Top with either the syntax

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

(Which is only supported by SQL Server 2005 as backward compatibility)

or

select top (10) id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Which is new in SQL Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you very much for this reply, and yes you are correct I didn't have to use 'set rowcount 10' it was removed within minutes of my previous post. Another coworker that had helped me on it yesterday showed me that you did not have to use it. So the second I got home (10 minutes ago) I updated the stored proc and it worked perfect. Now I just hope the rest of them go easier than that.

Thanks again every one.
Tim