Convert Access query with 'Last' to SQl Server

[supmktg]

I'm trying to create a View in SQL Server equivalent to an Access query that uses 'Last' in 2 columns in order to return only one (the last) of each record. Here is the query that I am trying to convert:

SELECT Max(tblH.HeadID) AS MaxHeadID, tblH.DeliverTo, tblH.Region, tblL.ProdNo, Last(tblH.PODate) AS LastDte, Last(tblL.Qty) AS LastQty
FROM tblH INNER JOIN tblL ON tblH.HeadID = tblL.HeadID
GROUP BY tblH.DeliverTo, tblH.Region, tblL.ProdNo;

I've searched a bunch of SQL Server forums but haven't found any examples that include 2 tables like above. Any help would be greatly appreciated!

Thanks,
Sup

[theDBguy]

Hi. Replay Last() with Max() and try it out in SS.

[CJ_London]

last has absolutely no meaning without an order. It certainly doesn't mean the last record entered although you might be lucky, you might not. You could run it twice and come up with different answers each time

[supmktg]

DBGuy,

Max does work for Last(tblH.PODate) because in this table the max PODate is also the record with the Max(tblH.HeadID). However, the Max(tblL.Qty) will return the largest qty in all records, not necessarily the record with the Max(tblH.HeadID).

Thanks,
Sup

[supmktg]

CL,

I neglected to include the Order By tblH.HeadID portion of my query, so you are correct that without that Order By last is worthless.

Thanks,
Sup

[theDBguy]

Hi. Sounds like you need a Top N Per Group query.

[CJ_London]

Even with the missing order by part of the code, I'm struggling to understand what the query is supposed to show.

what are you trying to achieve? - can you provide some example data and what you want the query to return.

with regards TSQL, there is no straight equivalent to Last, you use Last_Value with Over. I don't see why multiple tables is an issue. There are plenty of examples out there like this one using multiple tables

https://docs.microsoft.com/en-us/sql...ql-server-2017

you just don't need the partition part if you want last for the whole dataset

and I don't think you need the group by either - perhaps using distinct instead - you would use the Over for Max as well

here's another example

https://docs.microsoft.com/en-us/sql...ql-server-2017

[supmktg]

I'm fairly versed in Access sql but not so much in SQL Server, especially where it differs from or includes advanced functions that I don't use.

I've solved my problem by 1st creating a View so that all the fields that I need are in a single table. Then I used the ROW_NUMBER() OVER (PARTITION function (totally unfamiliar with) to accomplish what I needed.

For anyone who comes across this post where it might be helpful, here is the query that I built that returns the newest record of a group by sorting the rows by the incremental primary key in descending order and then choosing the row# 1 of each group:

SELECT *
FROM (SELECT [HeadID], [DeliverTo], [Region], [ProdNo], [PODate], [Qty], ROW_NUMBER() OVER (PARTITION BY [ProdNo] + CAST([DeliverTo] AS NVARCHAR(10)) + CAST([Region] AS NVARCHAR(10))
ORDER BY [HeadID] DESC) AS [ROW NUMBER]
FROM [vi_POProdDateQty]
) as groups
WHERE groups.[ROW NUMBER] = 1

The hardest part for me was concatenating the 3 fields that make up the grouping and casting the INT fields as CHAR to allow the concatenation.

[supmktg]

Hi CJ,

I've been working all day on the above solution and just saw your post. I greatly appreciate your assistance! After reviewing your 2 suggested posts, it seems as thought the Last_Value function was exactly what I was looking for. Unfortunately, none of my searches for the Access equivalent of LAST for SQL SERVER returned Last_Value. Fortunately, though, I did find ROW_NUMBER which returns exactly the records that I needed.

As for the multiple tables, I'm sure it could have been done that way but I hadn't found any examples to follow that used multiple tables and I was already overwhelmed in trying to understand the new logic and syntax to deal with joins.

Thank you again for your assistance!
Sup

[supmktg]

Hi DBGuy,

Top 1 was the first thing I tried, but I couldn't get it to work for my data set. I did finally solve it with ROW_NUMBER() OVER (PARTITION. If interested, see above post that includes the query that worked.

I greatly appreciate your assistance!
Sup

[CJ_London]

(PARTITION function (totally unfamiliar with)

Partition in T-SQL is different to the partition function in Access. In the latter it is used to identify values by set intervals - e.g. the partition function with an interval of 10 would return numbers 1 to 100 specified as being between 1-10,11-20,21-30 etc. Useful for graphs among other things.