Thursday, February 21, 2013 8:19 AM
I need to get values after top 12000 values..
without using rownumber () etc how can i get this?
what are the different ways?
Thursday, February 21, 2013 8:23 AMdoes table had identity or createdate (default getdate()) columns then you can order by desc on them to get information ......
Thursday, February 21, 2013 8:25 AM
If the table doesn't have an identity column, then i don't think it is possible
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
Thursday, February 21, 2013 8:48 AM
You could use EXCEPT, eg
SELECT name FROM sys.tables EXCEPT SELECT TOP 10 name FROM sys.tables ORDER BY name
Thursday, February 21, 2013 9:12 AM
If You have an IDentity column say ID with Integer with INcrement(1,1) then you can query by having a condition say
Where ID > 12000
Or if you dont have an identity column , check based on what you need top 12000 rows to be ignored, if you want to select those 12000 rows by name or any other column then do the order by desc based on that column.
DECLARE @CNT INT
SELECT @CNT = COUNT(*) FROM TABLE1
SET ROWCOUNT @CNT-12000
SELECT COL1, COL2 FROM TABLE1 ORDER BY COL1 DESC
The above query sample yields you entire data apart from top 12000
Thursday, February 21, 2013 9:23 AM
You may try the below:
Create Table T1 (Col1 Varchar(20)) Insert into T1 Select 'here' Insert into T1 Select 'there' Insert into T1 Select 'where' Insert into T1 Select 'Any' Select IDENTITY(int,1,1) RowID,* into #T1 From T1 Select * From #T1 Where RowID>2
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thursday, February 21, 2013 7:04 PM
A table is an unordered set of rows. So when you say "I need to get values after top 12000 values", you need to tell us what makes those 12000 values the top values.
If you don't care which values are part of the "top 12000" and which aren't, and you have a table with a unique clustered index, then it is best to write a query that uses the clustered index key to determine the order (a.k.a. to determine which rows below to the top 12000).
Apart from using a cursor or loop (which both would be extremely poor solutions) I don't think this is possible without using "rownumber () etc". Any efficient solution would use either ROW_NUMBER() or TOP. There might be appropriate windowing functions in the SQL specification, but none that are supported in SQL Server.
The most efficient method would be if you "know" the "key value" (according to your definition of top 12000) of row 12000. If your order is based on a unique clustered index, and you have established that row 12000 has the clustered index key 12345 (just an example), then it is as simple as writing "SELECT my_columns FROM my_table WHERE clustered_key > 12345"