dblankman
asked on
converting MS Access Last() to SQL Server
I have the following MS Access query
SELECT TurtleEvent.EventTurtleID, Last(AcWeighing.CCW) AS CCW
FROM TurtleEvent INNER JOIN AcWeighing ON TurtleEvent.EventID=AcWeig hing.Event ID
WHERE (((AcWeighing.CCW) Is Not Null))
GROUP BY TurtleEvent.EventTurtleID;
I need to convert this to sql server. I tried the following tsql:
SELECT distinct dbo.TurtleEvent.EventTurtl eID
,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.EventTurtl eID
,dbo.AcWeighing.CCW
ORDER BY TurtleEvent.EventTurtleID
BUT still getting duplicate eventID records
Partial results of query:
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
I want the result set to be:
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 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
SELECT TurtleEvent.EventTurtleID,
FROM TurtleEvent INNER JOIN AcWeighing ON TurtleEvent.EventID=AcWeig
WHERE (((AcWeighing.CCW) Is Not Null))
GROUP BY TurtleEvent.EventTurtleID;
I need to convert this to sql server. I tried the following tsql:
SELECT distinct dbo.TurtleEvent.EventTurtl
,max(TurtleEvent.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.EventTurtl
,dbo.AcWeighing.CCW
ORDER BY TurtleEvent.EventTurtleID
BUT still getting duplicate eventID records
Partial results of query:
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
I want the result set to be:
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 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pls try
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;
Regards