[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

converting MS Access Last() to SQL Server

Posted on 2014-02-03
2
Medium Priority
?
450 Views
Last Modified: 2014-02-03
I have the following MS Access query
SELECT TurtleEvent.EventTurtleID, Last(AcWeighing.CCW) AS CCW
FROM TurtleEvent INNER JOIN AcWeighing ON TurtleEvent.EventID=AcWeighing.EventID
WHERE (((AcWeighing.CCW) Is Not Null))
GROUP BY TurtleEvent.EventTurtleID;

I need to convert this to sql server. I tried the following tsql:
SELECT distinct dbo.TurtleEvent.EventTurtleID
      ,max(TurtleEvent.EventDate) as eventDate
      ,row_number() OVER (
            ORDER BY TurtleEvent.EventTurtleID
            ) AS row
      ,(dbo.AcWeighing.CCW) AS CCW
FROM dbo.TurtleEvent
INNER JOIN dbo.AcWeighing ON dbo.TurtleEvent.EventID = dbo.AcWeighing.EventID
WHERE (dbo.AcWeighing.CCW IS NOT NULL)
GROUP BY dbo.TurtleEvent.EventTurtleID
      ,dbo.AcWeighing.CCW
ORDER BY TurtleEvent.EventTurtleID

BUT still getting duplicate eventID records

Partial results of query:
EventTurtleID      eventDate      row      CCW
8      2003-04-15 00:00:00.0000000      1      35
9      2003-04-17 00:00:00.0000000      2      61
12      2002-04-02 00:00:00.0000000      3      8.7
12      2003-07-24 00:00:00.0000000      4      16.5
13      2003-03-21 00:00:00.0000000      5      5.4
18      2001-11-18 00:00:00.0000000      6      4.7

I want the result set to be:
EventTurtleID      eventDate      row      CCW
8      2003-04-15 00:00:00.0000000      1      35
9      2003-04-17 00:00:00.0000000      2      61
12      2003-07-24 00:00:00.0000000      4      16.5
13      2003-03-21 00:00:00.0000000      5      5.4
18      2001-11-18 00:00:00.0000000      6      4.7
0
Comment
Question by:dblankman
2 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39829311
Hi,

pls try

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;


Regards
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39829325
check this out
;WITH C AS
(
SELECT distinct dbo.TurtleEvent.EventTurtleID
      ,max(TurtleEvent.EventDate) as eventDate
      ,row_number() OVER (
            ORDER BY TurtleEvent.EventTurtleID
            ) AS row
      ,(dbo.AcWeighing.CCW) AS CCW
      ,row_number() OVER( partition by TurtleEvent.EventTurtleID order by TurtleEvent.EventTurtleID) as RN
FROM dbo.TurtleEvent
INNER JOIN dbo.AcWeighing ON dbo.TurtleEvent.EventID = dbo.AcWeighing.EventID
WHERE (dbo.AcWeighing.CCW IS NOT NULL)
GROUP BY dbo.TurtleEvent.EventTurtleID
      ,dbo.AcWeighing.CCW
ORDER BY TurtleEvent.EventTurtleID
)
SELECT * FROM C WHERE RN = 1

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question