| Author |
Topic |
|
jessiefun
Starting Member
35 Posts |
 Posted - 2010-11-10 : 01:04:46
|
| There is a table we can call it "Table1"it contains two columns - ID and Name ---------------------------------------Run the below scripts DECLARE @VAR1 INT, @VAR2 VARCHAR(10)SELECT @VAR1 = ID, @VAR2 = NameFROM dbo.Table1SELECT @VAR1 AS AA, @VAR2 AS BBQuestion: what's the result? The first row or last row in Table1 will be returened? |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-11-10 : 01:12:53
|
| Last one.PBUH |
 |
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-10 : 01:21:34
|
quote:
Originally posted by Sachin.Nand
Last one.PBUH
Yeah, got it. PUSH is the point. Thanks Sachine |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-11-10 : 01:32:57
|
quote:
Originally posted by jessiefun
quote:
Originally posted by Sachin.Nand
Last one.PBUH
Yeah, got it. PUSH is the point. Thanks Sachine
You are welcome.Also an order by clause will change the value returned.PBUH |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-10 : 02:27:47
|
| A row.There's no guarantee (without an order by) what order the rows will be returned in and as a result there's no guarantee what values you'll get back. A table, by definition, is an unordered set of rows, so asking about the first or last row of the table is a meaningless concept.If you want order, you must put an order by clause and only then can you talk meaningfully about order, first and last.--Gail ShawSQL Server MVP |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-10 : 04:36:08
|
quote:
Originally posted by GilaMonster
A row.There's no guarantee (without an order by) what order the rows will be returned in and as a result there's no guarantee what values you'll get back. A table, by definition, is an unordered set of rows, so asking about the first or last row of the table is a meaningless concept.If you want order, you must put an order by clause and only then can you talk meaningfully about order, first and last.--Gail ShawSQL Server MVP
Hi Gail, Thank you for your response.And I know what you mean. If we want to get something in order, we should put them in order manualy. While for this simple table - ID (Primary key) and another column "Name" and no other index. The result for the script above is definite. That's the last row. We can see that as a Stack, the push and pop thing. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-10 : 05:12:14
|
No. As Gail stated, there's no guarantee.If you want to say last row in this case then I agree with: last row from result set. Nothing else.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-10 : 05:29:30
|
quote:
Originally posted by webfred
No. As Gail stated, there's no guarantee.If you want to say last row in this case then I agree with: last row from result set. Nothing else.
No, you're never too old to Yak'n'Roll if you're too young to die.
Can you please take a look at this post? http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspxAnd there is one sentence: "The above trigger will only work for the last row in the inserted table."Can you make some explanation? The same? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-10 : 05:33:58
|
Yes. The same.If Tara comes along the she will agree, I am sure.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-10 : 05:53:25
|
quote:
Originally posted by webfred
Yes. The same.If Tara comes along the she will agree, I am sure.
No, you're never too old to Yak'n'Roll if you're too young to die.
Well, thank you then. Although, I didn't know why? Thanks, Jessie |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-10 : 05:58:22
|
quote:
A file is ordered and can be accessed by a PHYSICAL location, while a table is not. "first record", "last record", and "next n records" make sense in a file but not in a table.
See this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152748Last post of jcelko
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-10 : 08:44:01
|
quote:
Originally posted by webfred
quote:
A file is ordered and can be accessed by a PHYSICAL location, while a table is not. "first record", "last record", and "next n records" make sense in a file but not in a table.
See this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152748Last second post of jcelko
No, you're never too old to Yak'n'Roll if you're too young to die.
What happens if he replies to the post?  MadhivananFailing to plan is Planning to fail |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-11-10 : 09:03:57
|
| I wonder how does mysql determine "first record" or "last record' using Limit clause?PBUH |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-10 : 09:16:13
|
quote:
Originally posted by jessiefunAnd I know what you mean. If we want to get something in order, we should put them in order manualy.
No, if you want something in order, you need to specify and order by on the query that returns itquote:
While for this simple table - ID (Primary key) and another column "Name" and no other index. The result for the script above is definite. That's the last row.
The result is not definite. In this case, in the tests that you did, that was the 'last' row in the returned resultset. That is not guaranteed. Without an Order By, SQL is under NO obligation to return the rows in any particular order. What appears as the last row today may not appear as the last row tomorrowquote:
We can see that as a Stack, the push and pop thing.
A table is not a stack. A table is an unordered set.--Gail ShawSQL Server MVP |
|
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-15 : 01:07:47
|
quote:
Originally posted by madhivanan
quote:
Originally posted by webfred
quote:
A file is ordered and can be accessed by a PHYSICAL location, while a table is not. "first record", "last record", and "next n records" make sense in a file but not in a table.
See this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152748Last second post of jcelko
No, you're never too old to Yak'n'Roll if you're too young to die.
What happens if he replies to the post? MadhivananFailing to plan is Planning to fail
Yeah, Table is not like a stack. And I agree that a table is an unordered set. While for this simple table, it's ordered according to its primary key, the unique clustered index. And actutually, there is a loopoing-work for this assignment (from the first to the last), therefore, the value we get is the last one.  Thanks a lot for all your guys' information. Regards, Jessie |
|
|
|
|
First Row or Last Row?