Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 16th, 2007, 08:03 PM
Registered User
 
Join Date: Aug 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL to get all rows but top

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

Help would be appreciate

 
Old May 16th, 2007, 08:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

What do you mean by 'first'?

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old May 16th, 2007, 08:25 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old May 16th, 2007, 08:37 PM
Registered User
 
Join Date: Aug 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



 
Old May 16th, 2007, 08:57 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old May 17th, 2007, 07:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
 
Old May 17th, 2007, 07:59 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old May 17th, 2007, 08:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========
 
Old May 17th, 2007, 08:21 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old May 17th, 2007, 08:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
================================================== =========





Similar Threads
Thread Thread Starter Forum Replies Last Post
convert columns to rows in sql hoangmv0101 SQL Language 0 January 2nd, 2007 02:04 AM
Trying to retrieve top 1 for multiple rows MonicaM SQL Language 2 May 16th, 2006 03:29 AM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
Getting the Top 5 Rows from a datatable flyin General .NET 9 June 14th, 2004 08:36 AM
move rows to top with cells in columnA filled RED alienscript Excel VBA 2 December 2nd, 2003 12:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.