How to convert an Access Last function to SQL Server

Question

How to convert an Access Last function to SQL Server

stewsterl

SSC Eights!

Points: 844
More actions
March 31, 2010 at 11:00 am

#386616

Okay... I'm stuck. 🙁
Does anyone know how to get around / convert an Access Last function to SQL Server? I have the following query that I need to convert.
SELECT OPERATION_SCHED.SCHEDULE_ID, OPERATION_SCHED.RESOURCE_ID, OPERATION_SCHED.WORKORDER_BASE_ID,
OPERATION_SCHED.WORKORDER_SUB_ID,
Last(OPERATION_SCHED.SEQUENCE_NO) AS LastOfSEQUENCE_NO,
Last(DATEADD(d, 0, DATEDIFF(d, 0, [FINISH_DATE])) AS FINISH,
OPERATION_SCHED.WORKORDER_LOT_ID, OPERATION_SCHED.WORKORDER_SPLIT_ID
FROM OPERATION_SCHED
GROUP BY OPERATION_SCHED.SCHEDULE_ID, OPERATION_SCHED.RESOURCE_ID, OPERATION_SCHED.WORKORDER_BASE_ID,
OPERATION_SCHED.WORKORDER_SUB_ID, OPERATION_SCHED.WORKORDER_LOT_ID, OPERATION_SCHED.WORKORDER_SPLIT_ID
ORDER BY OPERATION_SCHED.WORKORDER_BASE_ID;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Not being an Access developer, I assume that the Last function returns the last (highest) value for a field? If so, try:

select top 1 "Your Field" from "Your Table" order by "Your Field" DESC

This reverses the order, putting the highest value at the top of the list. Getting the top 1 gets the highest value.

Hope this helps,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 2

The following code illustrates three possible solutions:

DECLARE @Table

TABLE (

group_id INTEGER NOT NULL,

data1 INTEGER NOT NULL,

data2 INTEGER NOT NULL

);

-- Two groups

INSERT @Table (group_id, data1, data2) VALUES (1, 1, 30);

INSERT @Table (group_id, data1, data2) VALUES (1, 2, 20);

INSERT @Table (group_id, data1, data2) VALUES (1, 3, 10);

INSERT @Table (group_id, data1, data2) VALUES (2, 4, 60);

INSERT @Table (group_id, data1, data2) VALUES (2, 5, 50);

INSERT @Table (group_id, data1, data2) VALUES (2, 6, 40);

-- Find the row data for each group

-- that has the lowest value of 'data1'

SELECT T.group_id,

T.data1,

T.data2

FROM @Table T

WHERE T.data1 =

(

SELECT MIN(T2.data1)

FROM @Table T2

WHERE T2.group_id = T.group_id

);

-- Implementation using ROW_NUMBER

SELECT T.group_id,

T.data1,

T.data2

FROM (

SELECT *,

rn =

ROW_NUMBER() OVER(

PARTITION BY group_id

ORDER BY data1 ASC)

FROM @Table

) T

WHERE rn = 1;

-- Implementation using APPLY

SELECT T.group_id,

iTVF.data1,

iTVF.data2

FROM (

SELECT DISTINCT group_id

FROM @Table

) T

CROSS

APPLY (

SELECT TOP (1) *

FROM @Table T2

WHERE T2.group_id = T.group_id

ORDER BY

T2.data1 ASC

) iTVF;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi