Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 03:22 PM   #1
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Convert Access query with 'Last' to SQl Server

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

supmktg is offline   Reply With Quote
Old 02-13-2019, 04:12 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Convert Access query with 'Last' to SQl Server

Hi. Replay Last() with Max() and try it out in SS.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 02-13-2019, 04:47 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,612
Thanks: 40
Thanked 3,434 Times in 3,325 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Convert Access query with 'Last' to SQl Server

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-14-2019, 01:22 PM   #4
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: Convert Access query with 'Last' to SQl Server

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 is offline   Reply With Quote
Old 02-14-2019, 01:22 PM   #5
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: Convert Access query with 'Last' to SQl Server

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
supmktg is offline   Reply With Quote
Old 02-14-2019, 01:23 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 919
Thanks: 8
Thanked 201 Times in 197 Posts
theDBguy will become famous soon enough
Re: Convert Access query with 'Last' to SQl Server

Hi. Sounds like you need a Top N Per Group query.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
supmktg (02-14-2019)
Old 02-14-2019, 03:57 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,612
Thanks: 40
Thanked 3,434 Times in 3,325 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Convert Access query with 'Last' to SQl Server

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
supmktg (02-14-2019)
Old 02-14-2019, 06:00 PM   #8
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: Convert Access query with 'Last' to SQl Server

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 is offline   Reply With Quote
Old 02-14-2019, 06:26 PM   #9
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: Convert Access query with 'Last' to SQl Server

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 is offline   Reply With Quote
Old 02-14-2019, 06:33 PM   #10
supmktg
Newly Registered User
 
Join Date: Mar 2002
Posts: 353
Thanks: 34
Thanked 3 Times in 3 Posts
supmktg
Re: Convert Access query with 'Last' to SQl Server

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
supmktg is offline   Reply With Quote
Old 02-15-2019, 02:53 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,612
Thanks: 40
Thanked 3,434 Times in 3,325 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Convert Access query with 'Last' to SQl Server

Quote:
(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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
supmktg (02-15-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert query from sql server to Access aadebayo Queries 0 05-20-2011 07:06 AM
how to convert Access 2007 table to sql server 2008 rdeshpa SQL Server 3 03-11-2010 10:21 PM
How to convert a Access application to SQL server application? marks2007 Modules & VBA 3 11-12-2007 09:05 AM
Help CONVERT Access PIVOT QUERY to SQL SERVER MsLady Queries 2 10-22-2007 10:07 AM
convert and sql query to access Mike Hughes Queries 3 09-15-2007 11:52 PM




All times are GMT -8. The time now is 05:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World