0

I have a view that may contain more than one row, looking like this:

[rate] | [vendorID]
 8374       1234
 6523       4321
 5234       9374

In a SPROC, I need to set a param equal to the value of the first column from the first row of the view. something like this: 

DECLARE @rate int;
SET @rate = (select top 1 rate from vendor_view where vendorID = 123)
SELECT @rate

But this ALWAYS returns the LAST row of the view.

In fact, if I simply run the subselect by itself, I only get the last row.
With 3 rows in the view, TOP 2 returns the FIRST and THIRD rows in order. With 4 rows, it's returning the top 3 in order. Yet still top 1 is returning the last. DERP?!?

This works..

DECLARE @rate int;

CREATE TABLE #temp (vRate int)
INSERT INTO  #temp (vRate) (select rate from vendor_view where vendorID = 123)

SET @rate = (select top 1 vRate from #temp)
SELECT @rate

DROP TABLE #temp

.. but can someone tell me why the first behaves so fudgely and how to do what I want? As explained in the comments, there is no meaningful column by which I can do an order by. Can I force the order in which rows are inserted to be the order in which they are returned?

[EDIT] I've also noticed that: select top 1 rate from ([view definition select]) also returns the correct values time and again.[/EDIT]

JoeBrockhaus
  • 2,745
  • 2
  • 40
  • 64
  • 4
    What do you define as `last` row. You're not ordering your result. SQL Server does not guarantee that the order is the same every time you execute a query, thus the `TOP 1` could be a different row each time you execute the query. – JodyT Mar 20 '13 at 23:06
  • that crossed my mind, but it never changes except for how I've noted. Of course I'm assuming that the last row inserted into a table will be the last row, which is may not be the case? But if all that is true, then the insert into the temp table should be just as unreliable. I suppose it's likely that the top 1 is affecting the optimizer and it's doing some funky ordering based on the tables and joins in the view itself? Still, I'd expect the temp table method to be just as unreliable. All that said, there is no way for me to sort this data to spoof the insert order (no PK, no dates, etc) – JoeBrockhaus Mar 20 '13 at 23:46
  • If you don't have an ordering column(s) to tell you which ones were inserted most recently, how do you know it wasn't returning incorrect results in the past? – UnhandledExcepSean Dec 27 '13 at 16:58

3 Answers3

1

That is by design.

If you don't specify how the query should be sorted, the database is free to return the records in any order that is convenient. There is no natural order for a table that is used as default sort order.

What the order will actually be depends on how the query is planned, so you can't even rely on the same query giving a consistent result over time, as the database will gather statistics about the data and may change how the query is planned based on that.

To get the record that you expect, you simply have to specify how you want them sorted, for example:

select top 1 rate
from vendor_view
where vendorID = 123
order by rate
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Unfortunately I have no column by which a sort would be meaningful. The order that vendors or rates are created (aka, the ids) is not the same as the order in which vendors and rates are associated to one another. unfortunately, the association table that the view is abstracting has no PKs, no dates, etc - all I have to go on is the order in which the data was inserted. Are you suggesting that the temp table method that seems to work is a fluke? – JoeBrockhaus Mar 20 '13 at 23:52
  • 1
    @JoeBrockhaus: Yes, it's just a fluke that you happen to get the result that you expected when you use the temporary table. If you reliably want to get the records in the order that they were added, you have to store some information about that in the table. – Guffa Mar 21 '13 at 00:11
  • On one hand, it's a fluke because it _shouldn't_ be consistent. On the other, it _is_ consistent, so not very flukey. :-s – JoeBrockhaus Dec 26 '13 at 22:02
  • @JoeBrockhaus: The problem is that it *isn't* actually consistent. Right now you get the result that you expect, because the database gets the records from the table in a specific way. When you add more data to the table, the database might at some point decide to fetch the data in a different way, and you get a different result. There is no natural order for the result, so there is no guarantee that the database will return them in the same order always. – Guffa Dec 27 '13 at 15:33
  • I understand the pitfalls & rationality of it, but I can tell you it _has been_ consistent for 8+ years with hundreds of thousands of rows added to the table. Obviously it's counter-intuitive, and should _not_ be consistent, and yet it has been. No sense arguing the theory behind it (I'm not making this up to piss you off) .. it's just strange and worth documenting. – JoeBrockhaus Dec 27 '13 at 16:44
  • FWIW, one thing omitted in the original question: this is SQL Server 2005. – JoeBrockhaus Dec 27 '13 at 16:45
  • @JoeBrockhaus: Yes, I know that it may seem to work consistently for very long, and you may actually never get any different result, but the documentation specifically guarantees no consistent result. In the beginning results had a natural order, but that was removed sometime around version 7, about 15 years ago. You seem to have a solution that is stable enough, but you might just want to comment the code with the fact that it relises on an undocumented feature. – Guffa Dec 27 '13 at 17:35
  • I agree. I did comment the code, and I write this code originally - just encapsulated it to be used elsewhere. I'd like to have been able to modify the schema but that was also outside the scope of what needed to be done. Ultimately the system to which this belongs was slated to be discontinued, so only minimal changes had to be made. – JoeBrockhaus Dec 27 '13 at 19:34
1

I ran into this problem on a query that had worked for years. We upgraded SQL Server and all of a sudden, an unordered select top 1 was not returning the final record in a table. We simply added an order by to the select.

My understanding is that SQL Server normally will generally provide you the results based on the clustered index if no order by is provided OR off of whatever index is picked by the engine. But, this is not a guarantee of a certain order.

If you don't have something to order off of, you need to add it. Either add a date inserted column and default it to GETDATE() or add an identity column. It won't help you historically, but it addresses the issue going forward.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
0

While it doesn't necessarily make sense that the results of the query should be consistent, in this particular instance they are so we decided to leave it 'as is'. Ultimately it would be best to add a column, but this was not an option. The application this belongs to is slated to be discontinued sometime soon and the database server will not be upgraded from SQL 2005. I don't necessarily like this outcome, but it is what it is: until it breaks it shall not be fixed. :-x

JoeBrockhaus
  • 2,745
  • 2
  • 40
  • 64
  • 1
    Or rather, it's not broken enough to be fixed. :) One important factor is that the exact consequenses are known if it would ever fail, which can't be said about all kludges. – Guffa Dec 28 '13 at 16:49