I need some help in writing a sql query to select all the rows except the first row.

Help would be appreciate

What do you mean by 'first'?

Jeff Mason
Custom Apps, Inc.
[email protected]

How do you define the first row? Do you have a unique value that can determine 1st from 2nd and so on??

If, for example, you have a PK that is an Auto increment field you could do something like:

SELECT * from [table] where pkValue > (SELECT min(pkValue) FROM [table))

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Thanks for quick reply.

My table doesn't have primary key.

There is a data in two columns in first row so I cannot use the primary key logic.

Then, back to Jeff's question, how does one define 'first' without a unique value? You need some sort of value to define a 'first' value in SQL, just because it appears as the first row in the table when you view it, you have no way to correlate that into SQL Syntax.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Doug is right about this.. but you can try:

select * from table MINUS select top 1 * from table (access syntax)
select * from table EXCEPT select top 1 * from table (sql server 2005 syntax)
select * from table NOT IN (select top 1 * from table) (sql standard syntax, but i think this will be very slow)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

This is an interesting approach Gonzalo, the only thing is you need to define a WHERE clause for this to work correctly.

In the latter of the 3 examples, SQL Server 2K will not execute the query as your NOT statement is out of place (It is not contained in a valid where clause or sub query).

One of the SQL Gurus might have a better solution for this, but I do not.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

hey doug...

the second example works ok on sql 2005. the first one not... it returned the two tables..

you are right about the third one.. but it can be replaced with:

select * from table where Not Exists (select top 1 * from table) (tested, and worked ok)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Very nice Gonzalo ^^ I have a question for you though, and this may be related to how my Database is set up, but when I execute this query against one of my tables, (26K rows) I return 0 results. Given, my databases are normalized so I have PK values and such, so does this query work only in a table that has the design that the OP described? (or something similiar to that)

Good stuff though ^^

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

which query are you trying?? this query should work on every table.. I don't have tables that big in my sql 2005 here.. maybe the thing that doesn't work is the select top 1, that specific for microsoft.. in oracle you add a where clause with rownum < 1...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========