I need to determine the last row number in a table with a SQL query.
Can't find anything on this. Help!

I'm starting to sound like a broken record. :D

What do you mean by "last"?

And what do you mean by "row number"?

Hint: Neither of these are valid concepts in SQL Server (2005 or otherwise)

Jeff Mason
[email protected]

Not sure your comment merits an answer. Thanks for the welcome.

The fact of the matter is that the concept of "last" is meaningless in a relational database. "Last"ness implies an ordering, and by definition, the rows in a table have no order, so without any information on what YOU mean by "last", your question is impossible to answer.

Further, there is no such concept of a "Row Number" in a table in SQL Server. Identity columns can be used, more or less, to simulate them, sort of.

You can't find anything on your particular problem because nothing exists for it. Your question is a non sequitor.

I said I'm sounding like a broken record because your question (and variations on it) is one that's been asked here many many times.

The conversation always starts with a question such as yours, then we get around to what you mean by "last" ("First", "Next", etc.), and then we eventually derive a query that gets you what you want in your particular situation.

Now, do you want to work to a solution, or do you want to whine because nobody jumped when you asked?

Your choice.

You're welcome.



Jeff Mason
[email protected]

Thank you for the second insult. I guess I now feel I am justified in returning the favor:

If you are too stubborn or do not possess the knowledge or common sense to answer questions humbly posed to you by people who know less about a subject in which you are a self-proclaimed expert, you should refrain from driving them away from this forum.

If you really did not understand the question because it was not structured in a manner that you, as a self-proclaimed authority felt it should have been, in order for you to understand it (particularly, when, as you say, this keeeps happening to you) then you should not have replied.

By the way, check out Microsoft's website, and you will see words such as "row" used by the very people who engineered (or is that the wrong word?- according to you) SQL. Then check the table data in a VB programming environment and you will see that there are rows; and guess what, one of them is the first one and another is the last one!

By the way, I posed the exact same question on another forum and got a very succinct answer which worked immediately. And they were very polite about it.


Could it be that you just do not know the answer? I challenge you to post it here if you do.


You might consider putting your time to better use.

Best regards.

I'm sorry you feel insulted. Despite what you may think, it was not my intention to do that. Please enlighten me. Which words in my replies do you consider an insult?

The notion of a "row" is a concept that certainly applies to a relational database. I never said anything about a "row". It was the concept of a "row number" that I was objecting to. Please reread what I said.

Believe me, I understand your question quite well; thank you for your concern. In fact, my prior comments clearly indicate that this question (or a variation) has been posted on this forum numerous times. Which means it has been answered many times.

This particular question, concerning SQL Server 2005, posted on an SQL Language forum is particularly interesting because you have so far refused to accept that your question is meaningless without some context. Instead, you choose to complain when this is pointed out to you.

I'm honestly trying to help you understand why what you've asked is meaningless. Despite your apparent thin skin, let me try to explain this again.

#1. SQL has no concept of a "row number". There is no such thing in a (true) relational database. Some implementations of SQL have extended the language, in a non-standard way, to include such a capability. Many would argue that defining such a concept flies in the face of the definition of what a relational database is. In any case, SQL Server isn't one of those implementations that has a "Row Number". I'm glad you were able to get an answer elsewhere. If the response included the use of a "row number", then it couldn't have been by using SQL Server.

#2. Rows in a relational database table are inherently unordered. The rows in a table in a relational database are members of a set (in the mathematical sense), and as such, members of a set are unordered. The upshot of this is that the database is free to deliver the rows that satisfy your query in any order at all, absent any specific ordering via an ORDER BY clause. This is true even though a particular tool you may use appears to order the rows; in fact, the order is simply dependent on the query processor, and that will do whatever it wants (unless, of course, you or the tool specifically request an order via an ORDER BY clause).

#3. The concept of "last" implies an ordering of the rows of a table. If I have a jumble of paper clips in a pile on my desk, which one is the "last" one? Given #2 above, the concept of "last" is therefore meaningless unless some ordering is imposed (i.e. if I place all the paper clips in a line, I can identify the "last" one I put there).

I'm curious as to what your use of the word "last" means in the context of your particular situation.

The implication of point #2 above is that if you execute a query such as:

    SELECT * FROM MyTable;

then there is no guarantee that the rows will be presented in any particular order. The query processor is free to present the rows in any order it chooses.

Now, if you have a column value which imposes an ordering of some form, such as, say, "LastProcessedDate", then it's simple to find the "last" date by using the MAX aggregate function, assuming, of course, that by "last" you mean the latest date. Note that this might not actually be the very "last" entry in the table (which of course, has no meaning, see above), if the application allows some form of backdating. But that aside, retrieving the row that corresponds to that latest date can be obtained via a subquery:

    SELECT * FROM MyTable
    WHERE LastProcessedDate = (SELECT MAX(LastProcessedDate) FROM MyTable);

You might consider using an identity column to impose a form of ordering. Then you could use a similar subquery to retrieve the largest identity value:

    SELECT * FROM MyTable
    WHERE MyIdentityColumn = (SELECT MAX(MyIdentityColumn ) FROM MyTable);

This takes advantage of the fact that identity values are monotonically increasing. But, they don't have to be. It's quite possible (though perhaps unusual) to define a table where identity values actually decrease (by setting the identity increment to a negative number). If that's the case, then the above will fail.

So you see, without knowing what it is you are trying to accomplish, and what data values you have in mind when you use the term "last", it is impossible to answer your question completely correctly.


Jeff Mason
[email protected]

You obviously have a lot of time to spend philosophizing, I don't. I am not a theorist, I am a doer (as I might venture to say are most people seeking help on this site-they need to get something done NOW-if they had time for theory, they would go to the library or take a class).

I already solved my problem elsewhere using the same exact posting-but you did not seem to want to accept that and you did not respond to my challenge to post your answer to my problem. Instead, you chose to write no less than 18 paragraphs, yes 18, and say nothing of any assistance to me. You are obviously not interested in helping people. You apparently just want to publish your views and hear yourself.

I normally would not have invested time in answering you. My first answer was two very short, polite sentences. Anybody else would have gotten the message that you had been rude and not helpful-and gone away or come back with a more diplomatic approach. But not you, you had to go on, an on, and on-just like I am doing right now, so...
Enough already! Goodbye-this time for good!

What is it they say about teaching a man to fish?

That's a concept somewhat over your head, I fear.

Jeff Mason
[email protected]

If by anybody else you mean everybody else on this forum, I strongly have to disagree. You not only have a thin skin, but you're a bad looser too.

IMO, Jeff has been extremely helpful and polite by giving you an excellent explanation of *why* your original question didn't make sense. I completely agree with him, both from a technical point of view, and from the way he tried to explain it to you.

You challenged him to post an answer to the question and I'd say he just earned the full 100 points for the correct answer. You're the one having a problem interpreting the answer and distilling something like "Ah, OK, so I need to determine what column in my dataset determines *my* view on order, and order by that.....

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

IMO, we may have to point such OPs to a standard link that you have replied earlier on similar topic (be it last row or first row or middle row or next to middle row, etc..), than composing such elaborative replies, so that let them first understand what they are trying to ask and if that really makes sense. I think this has taken a lot of time out of you jeff, already :)

_________________________
- Vijay G
Strive for Perfection