Do NOT ever use Top 1 (MSSQL, MySQL) or First 1 (Firebird) if you’re not sure, how to use it properly.
Developers, who use this construction, in most cases use it inappropriate way. Recently I’ve found 2 stored procedures, one for saving data and another is for reading.
Update Top (1) SomeTable set SomeField=SomeValue Where SomeID=@SomeParameter
Select ... (select Top 1 SomeField from SomeTable where SomeID=@SomeParameter) ... From ...
This SomeID field is neither Private Key, nor even Unique field in this table (don’t ask me about structure and another place for saving this field – it’s not possible here)
Yep. For the first sight it looks working.. But this is a huge delusion. When DB engine select the first row any developer should ask himself: How rows in this select is ordered and which row become the first one for now?
In fact – no one knows.
Some of tools, which is working with DB, sorting data on showing, but clients from code can work in different way. For example SSMS sorts data on select by first column but Firebird show data in order it was saved in table (by clustered index I think).
How can you guarantee, that after backup\restored or after inserting\deleting\updating rows in that table Top 1 will return the same row.
Good practice is – ALWAYS use ORDER BY with TOP 1, that’s how you’ll be sure about your top 1 row
And the second problem: Some of developers use TOP 1 as a additional check for their conditions.
Just imagine that you have some tables with attributes and their values. This values can be various for different dates
And some of developers write query like
select Top 1 Value from SomeTable where Name=@PName and @PDate between Date_from and Isnull(Date_to,'99990101')
When I asked them: Why you’re using TOP 1 here?
They answered me, that this is for cases, when you’ve got several rows.
HOW? How it can be?
Maybe developers better to add some constraints or additional checks in store procedures, to remove situations of several rows?
Just think a bit more about your data and how you work with it…
Wish you well