get after top 12000 rows without using Row_number etc

Question

Hi

I need to get values after top 12000 values..

without using rownumber () etc how can i get this?

what are the different ways?

kindly help

Thanks

With Regards

BI_Group

Answer 1

does table had identity or createdate (default getdate()) columns then you can order by desc on them to get  information ......

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

If the table doesn't have an identity column, then i don't think it is possible

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

You could use EXCEPT, eg

SELECT name
FROM sys.tables
EXCEPT 
SELECT TOP 10 name
FROM sys.tables
ORDER BY name

Answer 4

Hi BI_Group,

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

Regards

Naveen

Answer 5

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.

Answer 6

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"

---

Gert-Jan