Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert access first to SQL with multiple columns

Author  Topic 

mksdf
Starting Member

26 Posts

Posted - 2014-11-14 : 11:50:02
Hi
I am converting some access to SQL at work and I am having a problem with a query that uses the access FIRST function

My query looks like this in access


SELECT
TBL1.ID
, FIRST(TBL1.Code) AS FirstCode
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND Left(ID,1) IN('A','B','C','D','E')
GROUP BY
ID


I have tried to convert it using a slightly different method. So instead of picking the first value of the Code field that appears in the select, (which is what access would do and SQL can’t do as far as I’m aware). I first select the ID and code with the highest value associated with them. I hope this makes sense. The below script is what I did. I would’ve thought they would produce the same number of records but they don’t. Would anyone know a better method for the producing the acccess query result in SQL? Kind regards


SELECT
ID
, Code FirstCode
FROM
(
SELECT
ID
, Code
, MAX(Value) MaxValue
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND LEFT(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
, Code
) T1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-14 : 12:06:43
I'm not familiar with Access, but did you try the MIN function instead of MAX?

For performance reasons, don't put a function on the column in the where clause. It won't be able to use an index.

SELECT ID, MIN(Code) AS FirstCode
FROM TBL1
WHERE Marker = 'X' AND ID LIKE '[A-E]%'
GROUP BY ID

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-14 : 14:08:09
How about (SQL SERVER >= 2012):


SELECT
TBL1.ID
, FIRST_value(TBL1.Code) OVER(partition by ID ORDER BY (select 1)) AS FirstCode
FROM
TBL1
WHERE
WHERE Marker = 'X' AND ID LIKE '[A-E]%'


Note that the Access code did not specify any ordering, so I didn't either
Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2014-11-17 : 03:28:49
that's great. thank you to you both. i really appreciate it
Go to Top of Page
   

- Advertisement -