Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

840 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