First (last) function

  • Thread starter Thread starter RCL
  • Start date Start date
R

RCL

In access there is a first function. Is there anything the equivalent of
First() in Transact SQL?
 
I'm not familiar with the first function, but if you mean the first row
in a recordset, you would do SELECT TOP 1 * from table ....

If you can explain what the first function does, I can probably help
more.

Thanks,
Birgit
 
No, there is not.

The best solution is to include all relevant fields in the Group By list if
the value of the column is the same on all rows or to use a subquery for the
Group By with only the relevant fields in the other case.
 
First or Last function returns a field value from the first or last record
in the result set returned by a query.

I tried to use top 1 to get the same results but ran into the issue of
duplicates. See example below:

Please note that on IDToUse = 1158, top
1 works correctly because there are no duplicate values. On IDToUse = 1156
and 1157, top 1 returns multiple values.

SELECT PracSums.IDtoUse, PracSums.pname, PracSums.Claims, PracSums.Billed,
PracSums.Approved
FROM PracSums
WHERE (((PracSums.Approved)
In
(
SELECT TOP 1 [SubQ1].[Approved]
FROM [PracSums] AS [SubQ1]
WHERE [SubQ1].[IDtoUse] =[PracSums].[IDtoUse]
Order By [SubQ1].[Approved] Desc)))

Using sample data:
IDtoUse pname Claims Billed Approved
1156 TEST1 2 300 193
1156 TEST2 2 300 193
1156 TEST3 2 300 193
1157 Next1 1 80 63
1157 Next2 1 80 63
1158 Another1 0 0 1
1158 Another2 0 0 2
1158 Another3 0 0 3
1158 Another4 0 0 4
 
No, there's no First()/Last() in SQL Server 2000. For what you're trying to
do, it's gonna be a mess no matter what. There are two solutions that I can
see, though I'm unfamiliar with the specifics of Solution 2, so you'll have
to ask around if you decide to go that route.

Solution 1:

First, I'd recommend using a JOIN statement
Second, you're also going to have to include pname in your sub-query, since
it's the only thing that can create uniqueness within any IDtoUse/Approved
group (I'm assuming that there won't be duplicates within pname...if there
is, you're going to HAVE to use Solution 2, as far as I know).
Third, I think you want to use the MIN() function will work here, rather
than TOP 1/ORDER BY, though ultimately I think you could use either.
Finally, I think you actually need nested sub-queries.

I'm just writing off-the-cuff, so I don't promise my syntax is perfect or
that results will be what you expect, but I *believe* the following will do
what you want:

SELECT PracSums.IDtoUse, PracSums.pname, PracSums.Claims, PracSums.Billed,
PracSums.Approved
FROM PracSums INNER JOIN
(SELECT PracSums.IDtoUse, MIN(PracSums.pname) AS MinPname,
PracSums.Approved
FROM PracSums INNER JOIN
(SELECT PracSums.IDtoUse, MIN(PracSums.Approved) As MinApproved
FROM PracSums
GROUP BY PracSums.IDtoUse) AS SubQ2
ON (PracSums.IDtoUse = SubQ2.IDtoUse) AND (PracSums.Approved =
SubQ2.MinApproved)
GROUP BY PracSums.IDtoUse, PracSums.Approved) AS SubQ1
ON (PracSums.IDtoUse = SubQ1.IDtoUse) AND (PracSums.pname = SubQ1.MinPname)
AND (PracSums.Approved = SubQ1.MinApproved)


Solution 2:

You might be better off using a table-returning function where you loop
through a cursor and just pick off the first record in each grouping. I've
rarely ever used cursors, though, so someone else would have to tell you how
to do that.



Good luck,
Rob


RCL said:
First or Last function returns a field value from the first or last record
in the result set returned by a query.

I tried to use top 1 to get the same results but ran into the issue of
duplicates. See example below:

Please note that on IDToUse = 1158, top
1 works correctly because there are no duplicate values. On IDToUse =
1156
and 1157, top 1 returns multiple values.

SELECT PracSums.IDtoUse, PracSums.pname, PracSums.Claims, PracSums.Billed,
PracSums.Approved
FROM PracSums
WHERE (((PracSums.Approved)
In
(
SELECT TOP 1 [SubQ1].[Approved]
FROM [PracSums] AS [SubQ1]
WHERE [SubQ1].[IDtoUse] =[PracSums].[IDtoUse]
Order By [SubQ1].[Approved] Desc)))

Using sample data:
IDtoUse pname Claims Billed Approved
1156 TEST1 2 300 193
1156 TEST2 2 300 193
1156 TEST3 2 300 193
1157 Next1 1 80 63
1157 Next2 1 80 63
1158 Another1 0 0 1
1158 Another2 0 0 2
1158 Another3 0 0 3
1158 Another4 0 0 4




Datawonk said:
I'm not familiar with the first function, but if you mean the first row
in a recordset, you would do SELECT TOP 1 * from table ....

If you can explain what the first function does, I can probably help
more.

Thanks,
Birgit
Click to expand...
Click to expand...
 
Note that First/Last works as documented in
Access/Jet --- which is to say that it returns a
'random' value from the recordset. Normally the
'random' value is the 'first' value in a recordset,
but you get no guarantee.

That's why First/Last is so fast in Access/Jet --
it doesn't actually check the recordset to see if
the returned value is truly first/last.

(david)
 
Back
Top