Last 5 rows

Question

Last 5 rows

Viewing 15 posts - 16 through 30 (of 57 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Charmer (11/16/2011)
Hi Dev...did you check out with the query recommended by sumantaroy..?
i see no records as output...

I can't explain my pain. I am posting here in SSC & have no SQL Server (/ client) on my desk. :crying:

Charmer SSChampion Points: 14903 More actions · Answer 2

Dev (11/16/2011)
Charmer (11/16/2011)
Hi Dev...did you check out with the query recommended by sumantaroy..?
i see no records as output...
I can't explain my pain. I am posting here in SSC & have no SQL Server (/ client) on my desk. :crying:

oh....you don't have SQL....thats ok...

Thanks,
Charmer

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

alternative

DECLARE @NUMBER INT

SELECT @NUMBER = COUNT(*) FROM ATable

SET @NUMBER = @NUMBER -5

SELECT * FROM ATable WHERE ID NOT IN (SELECT TOP (@NUMBER) ID FROM ATable)

The reason its a random order is due to how the underlying data pages are spread out on the disk, MDF/NDF files are not cyclical and store data randomly on the disk, but hard drives read cyclical

eg.

1 object has 10 pages which could be spread out like this on the HDD

PAGE8 PAGE1 PAGE4 PAGE5 PAGE6 PAGE7 PAGE9 PAGE10 PAGE2 PAGE3

When the data is read it is read from Page8 first, so you will get information from page 8 before page 1, data is spread across the pages, if there is free space on a page SQL will use it without allocating a new page so newer data may exist on a page which has data from quite a while ago, that is why you need to use ORDER BY, otherwise your result set is non deterministic

Charmer SSChampion Points: 14903 More actions · Answer 4

the worst part is i don't have any primary column on my table...so i am not able to get any records...

Thanks,
Charmer

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

Charmer (11/16/2011)
the worst part is i don't have any primary column on my table...so i am not able to get any records...

First, why it is required?

Second, if it helps you to understand something create dummy table / data. How much time it will take? May be few minutes... 😀

Charmer SSChampion Points: 14903 More actions · Answer 6

Charmer

SSChampion

Points: 14903

November 16, 2011 at 8:37 am

#1408621

yeah....:-)....just few seconds...

Thanks,
Charmer

Ken McKelvey SSCoach Points: 18842 More actions · Answer 7

Charmer (11/16/2011)
Random Order...!!!?
but how come it gives same records every time when we execute the query..?
it must change the order right?

Under some curcumstances the order of a select statement, without an ORDER BY clause, will not appear random due to the way SQL Server holds the data. The point is that any apparent order is not guaranteed without an ORDER BY clause. (The actual order will depend on disk usage, cache, where the disk heads are etc.)

You probalby need to read some fundamental relational database theory.

eg http://en.wikipedia.org/wiki/Relation_(database)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Charmer (11/16/2011)
yeah....:-)....just few seconds...

Did it work as Expected?

Lowell SSC Guru Points: 323518 More actions · Answer 9

i'm trying to think this through, does this sound right to everyone?

SQL retrieves the data in what the query engine deems is the fastest way possible, and caches that execution plan.

Without an order by, it might SEEM like SQL server retrieves the data in some default order, or the same order multiple times in a row, but that order can change if any of the following things occur:

1. DATA is inserted,Updated or Deleted for the table at hand.

2. an index is added , dropped or changed.

3. an index rebuild or reindex command occurs.

4. statistics get updated.

5. any change on the server that may use a different plan(dbcc freeproccache, server restart, heavy load on the server, memory pressure)

6. More?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

Lowell (11/16/2011)
i'm trying to think this through, does this sound right to everyone?
SQL retrieves the data in what the query engine deems is the fastest way possible, and caches that execution plan.
Without an order by, it might SEEM like SQL server retrieves the data in some default order, or the same order multiple times in a row, but that order can change if any of the following things occur:
1. DATA is inserted,Updated or Deleted for the table at hand.
2. an index is added , dropped or changed.
3. an index rebuild or reindex command occurs.
4. statistics get updated.
5. any change on the server that may use a different plan(dbcc freeproccache, server restart, heavy load on the server, memory pressure)
6. More?

I am sure on #1. It's been tested in the thread below.

For #2, #3, #4 Please refer following. (Not only Sort Order but even result can change)

http://www.sqlservercentral.com/Forums/Topic1206466-391-1.aspx#bm1206869

I am doubtful on #5

My #6 would be Table Rebuild / Truncate (similar to #1).

Steve Malley Ten Centuries Points: 1204 More actions · Answer 11

You could use bcp and specify the first and last row (capital F and L parameters).

There is another function somewhere [could not find it immediately], I think using prepare and execute, that might provide another solution.

As others have pointed out, there is no order except as loosely established by clustering, or as otherwise determined by order by.

Jared SSC Guru Points: 61793 More actions · Answer 12

Lowell (11/16/2011)
i'm trying to think this through, does this sound right to everyone?
SQL retrieves the data in what the query engine deems is the fastest way possible, and caches that execution plan.
Without an order by, it might SEEM like SQL server retrieves the data in some default order, or the same order multiple times in a row, but that order can change if any of the following things occur:
1. DATA is inserted,Updated or Deleted for the table at hand.
2. an index is added , dropped or changed.
3. an index rebuild or reindex command occurs.
4. statistics get updated.
5. any change on the server that may use a different plan(dbcc freeproccache, server restart, heavy load on the server, memory pressure)
6. More?

I agree... The analogy I used with a developer here is this: "Just because you flip a quarter 1000 times and it comes up heads every time, does not guarantee it will come up heads when you flip it again." Maybe even better is to use this analogy with a 25 sided die. Depends on the person you are talking to 🙂

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 13

Ken McKelvey (11/16/2011)

Charmer (11/16/2011)
Random Order...!!!?
but how come it gives same records every time when we execute the query..?
it must change the order right?

Under some curcumstances the order of a select statement, without an ORDER BY clause, will not appear random due to the way SQL Server holds the data. The point is that any apparent order is not guaranteed without an ORDER BY clause. (The actual order will depend on disk usage, cache, where the disk heads are etc.)

You probalby need to read some fundamental relational database theory.

eg http://en.wikipedia.org/wiki/Relation_(database)%5B/quote%5D

So, the question is: "What do you want returned when to say the last 5 rows?" The last 5 rows inserted, the 5 highest id, the 5 highest datetime, etc.? Basically, with the architecture of SQL Server you have to fill in the blank to be a statement that reads "I would like to take the 5 highest records if the data was ordered by _____________."

Thanks,

Jared

Jared
CE - Microsoft

dave hants Default port Points: 1485 More actions · Answer 14

how do i select last 5 rows without any sorting order?

Do you have a business requirement behind this request? If so, what is it!

When you say the "last 5 rows" what do you mean. Do you mean the 5 rows that were most recently inserted or updated in your table? Or the first 5 rows that were inserted... Or something else?

What is your table structure and contents? And what is it that your user/customer really needs?

Rgds,

Dave.

Jared SSC Guru Points: 61793 More actions · Answer 15

david.moule (11/16/2011)
how do i select last 5 rows without any sorting order?
Do you have a business requirement behind this request? If so, what is it!
When you say the "last 5 rows" what do you mean. Do you mean the 5 rows that were most recently inserted or updated in your table? Or the first 5 rows that were inserted... Or something else?
What is your table structure and contents? And what is it that your user/customer really needs?
Rgds,
Dave.

Is there a parrot in the room? 🙂

Jared

Jared
CE - Microsoft