converting MS Access Last() to SQL Server FOLLOW-UP

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 as

I prematurely accepted as a solution the following:

;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

BUT this solution produces the first instance rather than the last instance

12      2002-04-02 00:00:00.0000000      3      8.7
INSTEAD OF
12      2003-07-24 00:00:00.0000000      4      16.5

Partial results of unpartitioned query:  The rows I want returned are in bold (these have the latest date):
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

161      2007-06-04 00:00:00.0000000      154      34
161      2007-05-09 00:00:00.0000000      155      34.1
161      2007-05-21 00:00:00.0000000      156      34.2
161      2007-07-02 00:00:00.0000000      157      34.5
161      2007-02-12 00:00:00.0000000      158      34.7
161      2007-08-27 00:00:00.0000000      159      35
161      2007-09-09 00:00:00.0000000      160      35.3
161      2007-10-08 00:00:00.0000000      161      35.4
161      2007-09-24 00:00:00.0000000      162      35.5
161      2007-06-18 00:00:00.0000000      163      35.7
161      2007-12-31 00:00:00.0000000      164      35.8
161      2007-12-17 00:00:00.0000000      165      36
161      2007-11-19 00:00:00.0000000      166      36.2
161      2008-01-28 00:00:00.0000000      167      36.3
161      2008-05-19 00:00:00.0000000      168      36.4
161      2008-02-25 00:00:00.0000000      169      36.5
161      2008-03-10 00:00:00.0000000      170      36.7
161      2008-04-21 00:00:00.0000000      171      36.8
161      2008-04-07 00:00:00.0000000      172      36.9
161      2008-06-16 00:00:00.0000000      173      37
161      2008-05-04 00:00:00.0000000      174      37.2
161      2008-06-30 00:00:00.0000000      175      37.8
161      2008-07-28 00:00:00.0000000      176      38.2
161      2008-09-25 00:00:00.0000000      177      38.5
161      2008-08-18 00:00:00.0000000      178      38.8
161      2008-12-08 00:00:00.0000000      179      39
161      2008-10-06 00:00:00.0000000      180      39.1
161      2009-06-15 00:00:00.0000000      192      42
161      2009-07-16 00:00:00.0000000      193      42.6
161      2009-07-28 00:00:00.0000000      194      42.8
161      2009-09-10 00:00:00.0000000      195      43
161      2009-08-28 00:00:00.0000000      196      43.3
161      2009-09-23 00:00:00.0000000      197      43.5
161      2010-10-19 00:00:00.0000000      217      48.8
161      2010-11-17 00:00:00.0000000      218      49.2
161      2011-03-25 00:00:00.0000000      219      49.5
161      2011-01-12 00:00:00.0000000      220      49.8
161      2011-02-24 00:00:00.0000000      221      50
161      2013-04-15 00:00:00.0000000      245      55.8
161      2013-02-07 00:00:00.0000000      246      56
161      2013-07-08 00:00:00.0000000      247      56.1
161      2013-04-01 00:00:00.0000000      248      56.2
161      2013-08-15 00:00:00.0000000      249      56.5
161      2013-08-06 00:00:00.0000000      250      56.6
161      2012-12-11 00:00:00.0000000      251      56.7
161      2013-01-03 00:00:00.0000000      252      57.2
161      2011-09-22 00:00:00.0000000      253      5308
162      2004-05-16 00:00:00.0000000      254      9
166      2004-05-15 00:00:00.0000000      255      7.6
dblankmanAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
check this out

;WITH C AS
(
SELECT distinct dbo.TurtleEvent.EventTurtleID  as    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
), C1 AS 
(
SELECT *, ROW_NUMBER() OVER(partition by EventTurtleID order by eventDate DESC ) RN1 FROM C 
)
SELECT * FROM C1 WHERE RN1 = 1

Open in new window

0
 
Surendra NathTechnology LeadCommented:
try this

;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
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
), C1 AS 
(
SELECT *, ROW_NUMBER() OVER(partition by EventTurtleID order by eventDate DESC ) RN1 FROM C 
)
SELECT * FROM C1 WHERE RN1 = 1

Open in new window

0
 
dblankmanAuthor Commented:
There are syntax errors in this code
0
 
dblankmanAuthor Commented:
Thanks, this time it works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.