| Author |
Topic |
|
macsterling
Yak Posting Veteran
56 Posts |
 Posted - 2008-12-17 : 20:56:58
|
| I have read some Google searches on it and am not sure I understand. The code I have says "select top 1 column1 from table1 where column2 is null. What is it asking? |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-17 : 23:09:54
|
| u will get the top 1 record of column1 from table1 when column2 is having null value col1 col21 null2 53 null4 78o/p u will get is 1 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2008-12-18 : 05:41:30
|
| Having "1" as the actual value in the sample table is a little confusing. So does top 1 mean the first "1" (not necessarily row1 but the first you encounter - should the example have had the first null in col2 not in the first row) select top 1 column1 from table1 where column2 is null does below give "jones"col1 col2-------------smith 2222jones nullrae 3434tall 8834short nullleft 4563 |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-18 : 05:49:36
|
| ur r getting the 1st record of col1 of urtable where col2 is having null top 1 means to get the top record in the queryif ur want short as output, then use select top 1 col1 from urt where col2 is null order by col1 desc |
|
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2008-12-18 : 05:52:04
|
| Yes It Gives "jones" as output because it is the first record it encounteredJai Krishna |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-12-18 : 06:02:52
|
| Why will you get the third id if you don't use an order by tara, I though that the top 1 clause would return just the first recordNot questioning you but just interested for my own knowledge |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-18 : 06:12:19
|
quote:
Originally posted by NeilG
Why will you get the third id if you don't use an order by tara, I though that the top 1 clause would return just the first recordNot questioning you but just interested for my own knowledge
top clause will give top record from the query |
|
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2008-12-18 : 07:31:56
|
| Thanks all. I couldn't find documentation that clearly explained it. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-18 : 07:35:31
|
quote:
Originally posted by macsterling
Thanks all. I couldn't find documentation that clearly explained it.
welcome  did u clarify ur doubt on top clause |
|
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2008-12-18 : 07:44:54
|
| Yes - Not exactly sure why "they" coded it this way in this context, but now I know what they are doing, if not why! |
|
|
|
soorajtnpki
Posting Yak Master
231 Posts |
Posted - 2008-12-18 : 07:47:02
|
quote:
Originally posted by NeilG
Why will you get the third id if you don't use an order by tara, I though that the top 1 clause would return just the first recordNot questioning you but just interested for my own knowledge
hi neil, me too have the same doubt... anybody please calrify....tanx in advance... |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2008-12-18 : 08:15:08
|
| Because SQL SERVER does not store the data the way you think it does. It may encounter the record that has '3' in it first, REGARDLESS of what order you input the data into the database. This is why you use an ORDER BY clause.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
soorajtnpki
Posting Yak Master
231 Posts |
Posted - 2008-12-18 : 23:36:25
|
quote:
Originally posted by DonAtWork
Because SQL SERVER does not store the data the way you think it does. It may encounter the record that has '3' in it first, REGARDLESS of what order you input the data into the database. This is why you use an ORDER BY clause.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp
ok tanx for your reply, then see table structure below.col1 col21 null2 53 null4 78so does it means col1=1 is not the top record for the case col2=nullso does it means select top 1* gives a random record to us.am i right?tanx in advance |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2008-12-19 : 07:22:32
|
| Not exactly random. It will give you the FIRST record it encounters, not a random one. To insure you get the exact order you want, you MUST use an ORDER BY clause.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-12-19 : 07:59:43
|
| Cheers guys |
|
|
|
|
What is "select top 1"