Solved

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

Posted on 2014-02-03
4
329 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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