Select n-1 row 1

[shailesh32]

Is there a way to select n-1 rows in a query, Which returns n rows?

 

[JamesLean]

I'm not sure I fully understand. Can you give an example of your query and what you want the result to be?

--James

 

[shailesh32]

When a select query is executed it returns n rows say 10.
But when we run the query i want 9(n-1) rows only to be selected.

 

[rajeevnandanmishra]

Hi,

I can not imagine the requirement. Try following, may be this will work for you:
----------------------------------------------------------
DECLARE @STRSQL VARCHAR(200)
SELECT * FROM SECTION_MST -- THIS IS YOUR SQL STATEMENT
SELECT @STRSQL = 'SET ROWCOUNT ' + CONVERT(VARCHAR(5),@@ROWCOUNT - 1) +
' SELECT * FROM SECTION_MST' -- THIS IS YOUR SQL STATEMENT
EXEC(@STRSQL)
----------------------------------------------------------

Cheers!

 

[cmhunt]

Which 9 rows?? Top 9, bottom 9 or random 9??
Easiest way to get top 9:

SELECT TOP 5 * FROM TableName

 

[cmhunt]

Sorry, I came up with

DECLARE @Rows AS varchar(10)
DECLARE @sql AS varchar(1000)
SET @Rows = (SELECT COUNT(*) - 1 FROM Contract)
SET @sql = 'SELECT TOP ' + @Rows + ' * FROM Contract'
PRINT @sql
EXECUTE (@sql)

But same sort of thing as rajeevnandanmishra

Chris

 

[sunila7]

Hi,

I think u can avoid dynamic query here..... something like this
declare @noofrows int
select * from authors
set @noofrows =@@rowcount-1
SET ROWCOUNT @noofrows
Select * From authors



Sunil