Access 'First Function' in SQL Server

[jakem]

Does anyone know of a nice alternative to MS Access' 'First Function' in SQL Server. For instance, if I run the following query in Access it will return the first row of data in the Title table:

Select First(Title) As FTitle, ISBN, Call_Number
From Title

However, if I try to run this in SQL Server it tells me that ''First' is not a recognized function name.'

Any suggestions would be greatly appreciated!!!

 

[tlbroadbent]

There is no FIRST function, obviously. FIRST is really an aberration. It is not a Relational concept as relational databases do not have any order by definition. About as close as you can get is if you have an identity column or a datetime column on the records so you can choose the MIN of that column. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[chrislarkin]

You could use:

Select TOP 1 Title As FTitle, ISBN, Call_Number
From Title

that will give you the first row as stored in the database.

 

[tlbroadbent]

A Top 1 query, without an ORDER BY clause, will return the first row on the first page of the table. That row will not necessarily be the first row stored into the table.

For example, if table has a clustered index, the data is physically sorted in the order of the index and written to the correct page. Suppose the clustered index is on the EmployeeID column and the following rows are stored in the table.

EmployeeID TransCd TransDate
100021 1 9/17/01
100003 3 9/18/01
100020 2 9/19/01
100001 2 9/19/01

The physical order of the data will be as follows.

EmployeeID TransCd TransDate
100001 2 9/19/01
100003 3 9/18/01
100020 2 9/19/01
100021 1 9/17/01

Select Top 1 * From Table will retun the following.

100001 2 9/19/01

However, the first record entred on the table was

100021 1 9/17/01

We can see from this that Select Top 1 in MS SQL Server will not return the same result that Select First(ColumnName) returned in MS Access.

Now consider what happens if a new clustered index is chosen. The data will be physically reordered again, possibly resulting in yet another record being selected as Top 1.

The concept of FIRST and LAST are simply not part of the Relational model. SQL Server doesn't support the concept. One reason for is because data order can be readily changed as I've shown. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[moonshadow]

Terry's objections aside, supposing you pre-order your recordset with a view. Wouldn't it be useful if you could then do something like this:
Imagine a table tblData with GroupID, KeyValue & ProcessDate fields
Define a view v_SomeView as:
Select GroupID, KeyValue from tblData order by GroupID, ProcessDate DESC
Wouldn't it be great if you could do this:
Select GroupID, First(KeyValue) from v_SomeView group by GroupID
That way you could pull out the KeyValue for the latest ProcessDate for each GroupID
I'm occasionally wanting to do this - if anyone has any ideas, I'd be very grateful. At the moment I'm having to create an additional view which has the Max(ProcessDate) for each GroupID.

 

[rac2]

Could that be done with a correlated view?

SELECT GroupID, KeyValue FROM v_SomeView a
WHERE ProcessDate =
(SELECT MAX(ProcessDate) FROM v_SomeView b
WHERE b.GroupID = a.GroupID)

 

[moonshadow]

You are correct, it can - but it's very slow in the case I'm using it. I found that having a view, v_MaxView, defined as:
select GroupId, max(processdate) as MaxProcessDate from tblData group by GroupId
and then joining it back to tblData thus:

select GroupId, KeyValue from tblData inner join v_MaxView on tblData.GroupId=v_MaxView.GroupId and tblData.ProcessDate=v_MaxView.MaxProcessDate

is considerably faster. Of course, my real life scenario is slightly more complicated than this. If only we had a FIRST and LAST equivelant.