Select last row without an ID?

Strange thing come up today.

Need to select the last row of a table without an id.

Obviously would normally do max(id) but without an id column, how do you do it?
In Access you can do LAST, Sql has TOP, but nothing that I can find for Last, or Bottom or something?

Thanks
Andy
LVL 1
andy_boothAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
use TOP with a descending sort order
0
 
andy_boothAuthor Commented:
I have nothing that I can sort by to get the data reversed though.

That's my whole problem.
0
 
Brian CroweDatabase AdministratorCommented:
so how do you determine what is the last if you have no qualifier?
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Kevin3NFCommented:
You can't do it without something to specifically tell SQL Server what 'last' means.  The data is not physically stored in any order unless there is a clustered index on the table
0
 
andy_boothAuthor Commented:
Everything is stamped with a SQL row ID. I am trying to see if it can be done via this or other @@ Saying it can't be done is a bit weak, there will be a way, please don't dismiss just like that.
0
 
Kevin3NFCommented:
So you are saying you do have a rowID?  If so, then max(id) works....if not.....can't be done is not weak, it is accurate.

If I show you a parking lot and tell you to pick out the last one parked....how would you do it?
0
 
Scott PletcherSenior DBACommented:
Honestly, it really cannot be done.  SQL does have a "row id" of sorts, but it is based on page number and row number, and since SQL is *not* guaranteed to store records "at the end" of a table (the last unused/partial page), unless there is something within the data itself to determine which rows was inserted "last", there is no way to tell.  The parking lot analogy is very good and apt.

0
 
andy_boothAuthor Commented:
Agreed, with the theory, but if top works, there must be some way to do it.

One way would be to load it into a temp table with a id, then select from that.
All I am saying is there are ways around it and I am looking for the easiest to implement.
0
 
Kevin3NFCommented:
If you load into a table with an ID, you can certainly get the last row.  But there is still no way to know which row it is.

For the TOP clause to have any relevance, an order by or relevant where needs to be included as well
0
 
Brian CroweDatabase AdministratorCommented:
TOP requires an ORDER BY clause to have any meaning otherwise you could potentially run the query

SELECT TOP 1 * FROM myTable

and get different results each time.
0
 
Anthony PerkinsCommented:
>>All I am saying is there are ways around it and I am looking for the easiest to implement.<<
Nope.  When you notice that it hurts to hit your head against the wall, than it is time to stop.
0
 
Anthony PerkinsCommented:
Also, please maintain these abandoned questions:

1 02/21/2005 50 Event Log Error  Open Windows 2000
2 12/05/2004 50 Win2k3 Desktop Firewall?  Open Windows Server 2003
3 12/29/2004 250 Windows 2000 Server Keeps Rebooting  Open Windows 2000

Thanks.
0
 
Anthony PerkinsCommented:
Do you still need help with this question or can we now consider it abandoned?
0
 
andy_boothAuthor Commented:
No one has answered it.

Many have said it cant be done, when one possible solution would be to put it into a new table in a select and add an id.

I left it open becuase there are possible solutions albeit not simple ones and thought someone else might post something.
0
 
Kevin3NFCommented:
unsub
0
 
Anthony PerkinsCommented:
0
 
andy_boothAuthor Commented:
Do whatever you think is right.

IMO no one has given an acceptable answer here.
I am not going to give someone points for saying "it can't be done" thats not a solution.
0
 
Anthony PerkinsCommented:
>>I am not going to give someone points for saying "it can't be done" thats not a solution.<<
Than post a comment to that effect in Community Support including a reference to this question as per the link I posted.  See here for more info:
Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/help.jsp#hi71
0
 
Anthony PerkinsCommented:
Split the points among all contributors.
0
 
Anthony PerkinsCommented:
Oops that is not posible, this question is only worth 50 points.  Award the points to BriCrowe.
0
All Courses

From novice to tech pro — start learning today.