How to convert First Function from MS Acess to T-SQL

sqlman08
sqlman08 used Ask the Experts™
on
Hi,
I have the following query from MS Access Database how do I convert this into Sql Server 2005, as it does not use the First Function.

SELECT
      TableA.OrderRef,
      First(TableA.AssetTag) AS FirstOfAssetTag,
      First(TableA.SerialTag) AS FirstOfSerialTag,
      TableA.CategoryId,
      TableA.Type,
      TableA.Description

FROM
      TableA
GROUP BY
      TableA.OrderRef,
      TableA.CategoryId,
      TableA.Type,
      TableA.Description
HAVING
      (((First(TableA.AssetTag))>"1")
      AND ((First(TableA.SerialTag)) Is Not Null)
      AND ((TableA.CategoryId)<>"Monitor"
      And (TableA.CategoryId)<>"BO")
      AND ((TableA.Type)="y" Or (TableA.Type)="e" Or (TableA.Type)="p"))
ORDER BY
      TableA.OrderRef


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
you will have to rely on Top 1 sorted by some column of your preference. It should be something like this
SELECT 
      	a.OrderRef, 
	(select top 1 AssetTag from TableA where OrderRef = a.OrderRef order by somecolumn) FirstOfAssetTag,
	(select top 1 SerialTag from TableA where OrderRef = a.OrderRef order by somecolumn) FirstOfSerialTag,
      	a.CategoryId, 
      	a.Type, 
      	a.Description 

FROM 
      TableA a
WHERE
      not exists (select 1 from TableA where OrderRef = a.OrderRef and AssetTag = 1 and SerialTag is null)

      AND a.CategoryId<>'Monitor'
      And a.CategoryId<>'BO'

      AND (a.Type='y' Or a.Type='e' Or a.Type='p')


GROUP BY 
      a.OrderRef, 
      a.CategoryId, 
      a.Type, 
      a.Description
ORDER BY 
      a.OrderRef

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi,

Since FIRST() in Access is not determinate at all, it randomly returns the first value it comes across when collecting data (depending on your query and the plan used, it can return different values even against the same table), you might as well go for good old Min/Max.


SELECT
      TableA.OrderRef,
      Min(TableA.AssetTag) AS FirstOfAssetTag,
      Min(TableA.SerialTag) AS FirstOfSerialTag,
      TableA.CategoryId,
      TableA.Type,
      TableA.Description
FROM
      TableA
GROUP BY
      TableA.OrderRef,
      TableA.CategoryId,
      TableA.Type,
      TableA.Description
HAVING
      (((Max(TableA.AssetTag))>"1")
      AND ((Min(TableA.SerialTag)) Is Not Null)
      AND ((TableA.CategoryId)<>"Monitor"
      And (TableA.CategoryId)<>"BO")
      AND ((TableA.Type)="y" Or (TableA.Type)="e" Or (TableA.Type)="p"))
ORDER BY
      TableA.OrderRef
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Hi ralmada, I am not getting the same results as what is in acccess?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
For what it's worth,

   (((First(TableA.AssetTag))>"1")
      AND ((First(TableA.SerialTag)) Is Not Null)

is not equivalent to

      not exists (select 1 from TableA where OrderRef = a.OrderRef and AssetTag = 1 and SerialTag is null)

The 2nd explicitly looks for the existence of any SINGLE record with an asset tag of 1 and serialtag of null.

The 1st (Access First()) checks, for the first record it comes across in any GROUP,
 (1) is AssetTag >"1" (textual comparison)
 (2) is SerialTag null
Even if there is a record that match this criteria in the GROUP, if it is not the FIRST, no cigar.

Author

Commented:
hi cyberkiwi, does it not find the First based on what you are ordering by? (btw I have not tried your query yet)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
>> I am not getting the same results as what is in acccess?

FIRST() in Access is not determinate at all, it randomly returns the first value it comes across when collecting data (depending on your query and the plan used, it can return different values even against the same table).

Unlike Sql server, you cannot specify any ORDER BY for FIRST() and LAST().

Use FIRST and LAST only when you really do not care which record, except it is one from the list.  Normally (about 95% true), it is the record first/last entered in the the table. e.g. Create a table with 2 fields

GroupName,Value
G1, B
G1, A
G1, C

If you do

select groupname, first(Value), Last(Value) from tbl group by groupname

you are likely to get G1, B, C
(B is entered first, C is entered last)

But for more complex queries involving joins with many other tables, the table data could be accessed in different ways, and you may get C for first and A for last.

Author

Commented:
so what do you suggest? The access query is not going to join to any othere tables
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
http://www.eggheadcafe.com/software/aspnet/30462332/first-in-ms-access.aspx

"
I think you are wrong about FIRST in Access. The documentation makes it
clear that FIRST is essentially a non-deterministic function. It MAY return
the value from the first row in the order they were inserted but that
assumed order could easily be subject to change. For all practical purposes
FIRST returns a non-deterministic result.

I noticed that prior to Office 2007 the Access help was perfectly clear that
FIRST returned a RANDOM value. For some reason that description has changed
in Office 2007 and it is now far from clear what the correct use of FIRST is
supposed to be. Even so, nothing about the function itself appears to have
changed. Despite the fact that help mentions ORDER BY it seems to be
impossible for a query to specify what is actually meant by the "first" row
(because ORDER BY can only refer to the columns not being aggregated). I can
only conclude that FIRST/LAST is not capable of determining the first or
last value using any determinstic, logical method.

--
David Portas, SQL Server MVP
"
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
For a 90-95% correct answer, does your table have an identity field?

Author

Commented:
cyberkiwi, i have tried your query but get the following error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'AE812802            ' to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

Author

Commented:
Orderref would be the primary key i think
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Orderref is not unique is it?  Going by the query I don't think it is.
SELECT OrderRef, CategoryId, Type, Description, AssetTag, SerialTag
FROM
(
SELECT
      TableA.OrderRef,
      TableA.CategoryId,
      TableA.Type,
      TableA.Description,
      TableA.AssetTag,
      TableA.SerialTag,
      rn=Row_Number() over (
        partition by TableA.OrderRef, TableA.CategoryId, TableA.Type, TableA.Description
        order by TableA.OrderRef -- , TableA.ID ASC
      )
FROM
      TableA
WHERE (TableA.CategoryId<>'Monitor' And (TableA.CategoryId)<>'BO')
      AND ((TableA.Type='y') Or (TableA.Type='e') Or (TableA.Type='p'))
) A
WHERE rn=1
AND AssetTag>"1"
AND SerialTag Is Not Null
ORDER BY 
      OrderRef

Open in new window

Author

Commented:
cyberkiwi, I've tried the above query but get the following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'AE810147            ' to data type int.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
At the end, please replace

AND AssetTag>"1"

with

AND AssetTag>'1'

Author

Commented:
yeah, i've already done that but still get the error
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
What are the column types and where does that data look like it is coming from? An asset tag?

AND AssetTag>'1' (quote the 1) not AND AssetTag>1

Author

Commented:
sorry it works now

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial