Solved

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

Posted on 2014-02-03
4
337 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 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
0
Comment
Question by:dblankman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39829557
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
 

Author Comment

by:dblankman
ID: 39829599
There are syntax errors in this code
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39829613
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
 

Author Closing Comment

by:dblankman
ID: 39829635
Thanks, this time it works
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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