Select FIRST to get a particular record in a query
Hint Ref: 020909110002
Hint Date: 11/09/2009
Hint Details:
Imagine that a report required the MovementID of the oldest movement record for a particular SubContract record.
Often this requirement is split into two sub-queries;
One to return the oldest Movement StartDate for that SubContractID
and
One to return the actual MovementID for the first record for that SubContractID with the returned StartDate.
E.g.
(Select FIRST MovementID from Movement where SubContractID=<the passed in SubContractID> and Startdate= (Select min(StartDate) from Movement where SubContractID=<the passed in SubContractID>)) as MovID
If you wish to make a sub-select of one particular row or field, but in a situation where the WHERE would need to be able to include analysis of data from the same table, Select FIRST is not the appropriate command;
Instead use the command: Select TOP 1.
By using this select command in conjunction with an 'Order By' statement, you can force the required row to be at the very top of the retuned dataset and thus get the data you require by selecting the very TOP record only.
For the example above, the query could be rewritten thus:
Select Top 1 MovmentID from Movement where SubContractID=<the passed in SubContractID Order By StartDate Asc