Why everyone wants to use Top 1?

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.

Saving Part

Update Top (1) SomeTable
set SomeField=SomeValue
Where SomeID=@SomeParameter

Reading Part

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


You May Also Like

About the Author: admin