?
Solved

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

Posted on 2014-02-03
4
Medium Priority
?
347 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 2000 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

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 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