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 17th, 2007, 08:31 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

My query is exactly:
select * from consumer where Not Exists (select top 1 * from consumer)

And it returns 0 records. (Like i said, my rows contain unique values so this may be fudging things up)

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

if you try the second query (select top 1...) it return only the first row??

if you try this:
select * from consumer where Exists (select top 1 * from consumer)
it only return the first row?

you have a nice point to see.. this should work ok.. can you give me a desc of your table so i can fill with dummy data and try here???

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

I can't use the second query as I do not have SQL2K5. Doing a select * from consumer where Exists(Select top 1* form consumer) returns ALL rows from the table.

the table structure is like this

pk (int, Not Null, identity, auto inc)
entryDate (smalldatetime, not null)
entryLogon_fk (int, not null)
FirstName (varchar(32), not null)
MidName (varchar(32), null)
LastName (varchar(32), not null)
nameSuffix (varchar(10), null)
birthdate(smallDateTime, null)
gender_fk(char(1), not null)
ethnicity_fk(int, null)
SSN (varchar(9) not null)
[agencyID] (int, not null) [Always a unique value]
id2 (int null)
id3 (int null)
id4 (int null)
historyFlag (bit, not null)
updateFlag (bit, not null)
AliasFor (int, null)
AliasFrom (datetime, null)
AliasTo (datetime, null)




================================================== =========
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, 09:52 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

yeah I was trying that and somewhere i made a mistake, because now the last query is returning 0 in every case (using adventureworks database now to try it)...
I analyse the query plan for this 2:

select * from dia where not exists (select top 2 * from dia);
select * from dia except (select top 1 * from dia);

dia is a table that has the days of the week (id, descripcion) (descripcion is the name of the day)

the execution plan shows that in the case of the not exists there is a warning that say that is missing a join predicated...
in the case of the except there is no warning and the nested loop has build the predicate itself joining the 2 results with all the fields... the funny thing is that the predicate do an equal on every field, but I don't see where the query analizer exclude the rows...

i solved the problem building this query:
select a.id, a.descripcion from dia a inner join (select top 1 * from dia) b on a.id <> b.id and a.descripcion <> b.descripcion

if you have a PK fields just add them and it will resolve the query ok... but this is a tricky thing to do since you can do it with the first row only, if you try to take out the first and second row this query doesn't do the trick... but it will be interesting to find a way to do this that doesn't involved T-Sql or a store procedure...

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, 09:57 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

Ok just so long as I am not losing MY mind ;] In any case, I participated in a thread a few months ago where the OP had the exact same problem but I can't seem to find it. I will keep looking though. (Great work btw 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
================================================== =========
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, 10:05 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

Ok, I found the thread I referenced in my last post, here is the most important point:

Quote:
quote:Originally posted by Jeff Mason



Let's assume that there are a number of rows all with the same number of (minimum) calls. These rows together form a set.

When you say you want "The first one" from this set, the word "first" is an ordinal number. This implies that the set is ordered in some way. If there is no ordering, then what do you mean by "first"? If there is no ordering, then any row at all could be first, last, or anything in between. Actually, if there is no ordering, then such concepts as "first", "last", or "twenty-seventh" have no meaning at all.

Indeed, in SQL, the rows which form the resultset from a query, absent any ORDER BY clause, are an unordered set, and the query processor is free to return that resultset in any order it wants, or rather, in no order at all. You must not make any assumptions about the order of a resultset unless you have specifically indicated you want an ordering via the ORDER BY clause.

When you say "first", you are (probably) referring to the row which "comes before" all the other rows. In order to determine that, there must be something which determines the "come-before-ing" property of a row. Typically, this sort of thing would be a date, such as a "LastActivityDate", or maybe "LastCallDate".

Now, if you have such a column in your rows, you could order the resultset from the minimum query we've discussed by that column, then use the TOP 1 qualifier to extract the "first" row as ordered by that date column.

Thus, you'll need to have some value which can be used to order the resultset. Once you ORDER BY that column value, then such concepts as "first" and "last" have a meaning.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Of course, Jeff is pointing out on how to retrieve the first row when there is no unique identifier but the same logic applies. If you can't order by anything, you can't define a First, second, etc.

================================================== =========
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, 10:18 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

Thanks doug :) jeff has a great point there (that you bring to life before in the thread). usually the first row is in stored order (the first that you insert).. maybe the user want to filter that row because has empty data (I saw some designs where tables has a blank row so when filling combo boxes the user can select blank data) awkward, but the case is maybe to exclude them when you show all the possibles selection to the user..
anyway this can be sorted out easily just selecting every row that doesn't have that column blank, but there could be the case when the first row has something besides a blank, and it happends in several tables and you want a generic select for that type of tables...

Still better have a PK.

there is smoke around here.. maybe it's my head...

what's OP???

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, 10:26 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

OP == Orignal Poster.

I agree, designing tables with a PK is just good design (read: normalization) and it makes problems like this a trivial matter.

In so far as having a blank row available for display in a combo box, I can think of about 5 ways to achieve the same effect without needing a physical blank for in the database, but to each their own.

We haven't heard back from the OP in awhile, i think we may have scared him with all of our rambling!

================================================== =========
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, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Well! OP should be reading all this like me, preparing to ask his next question ;)

_________________________
- Vijay G
Strive for Perfection
 
Old May 17th, 2007, 03:52 PM
Registered User
 
Join Date: Aug 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey guys,

Thanks for your help.








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.