Convert access first to SQL with multiple columns

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/

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

mksdf
Starting Member

26 Posts

Posted - 2014-11-17 : 03:28:49

that's great. thank you to you both. i really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources