TOP Record By Group

MBoy
MBoy used Ask the Experts™
on
The following SP works fine for grabbing all the records I need.  I need to modify this SP to return only the TOP [Event].[2] per group.  Thanks in advance.
CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 
	 SELECT
		[GPRSDevice].[GPRSDeviceHardwareID],
		[Vehicle].[VehicleName],
		[Event].[4],
		[Event].[3],
		[Event].[2]
     FROM  [GPRSDevice], [Vehicle], [Event]
	 WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
		AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
	 ORDER BY [Vehicle].[VehicleName]
 
END
GO

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 ;WITH CTE AS
(

       SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2],
            ROW_NUMBER() OVER (PARTITION BY [Event].[2] ORDER BY [Event].[2]) AS 'RowNo'

     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
       
 )
 
 SELECT [GPRSDeviceHardwareID], [VehicleName], [4], [3], [2]
 from CTE
 where RowNo = 1
 ORDER BY [VehicleName]
END
GO
SharathData Engineer

Commented:
I think you need this. In the ORDER BY clause of ROW_NUMBER() function, add DESC if you want.
CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 ;WITH CTE AS 
(
 
       SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2],
            ROW_NUMBER() OVER (PARTITION BY [GPRSDevice].[GPRSDeviceHardwareID],    [Vehicle].[VehicleName], [Event].[4], [Event].[3]
                               ORDER BY [Event].[2]) AS RowNo 
 
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
       
 )
 
 SELECT [GPRSDeviceHardwareID], [VehicleName], [4], [3], [2]
 from CTE 
 where RowNo = 1 
 ORDER BY [VehicleName]
END
GO

Open in new window

Author

Commented:
Both of these solutions are returning multiple records for each group.  I'll try making some changes... any additional help will be appreciated.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
Here is the result of Sharath_123


025406657      Backhoe      27.981790      -82.507753      10/22/2009 4:34:19 PM
025406657      Backhoe      27.981863      -82.507816      10/22/2009 7:13:21 PM
025406657      Backhoe      27.981865      -82.507883      10/22/2009 10:34:19 PM
025406657      Backhoe      27.981913      -82.507900      10/21/2009 11:04:41 PM
025406657      Backhoe      27.981938      -82.507870      10/22/2009 5:54:44 PM
025406657      Backhoe      27.981943      -82.507895      10/21/2009 10:32:03 PM
025406657      Backhoe      27.981945      -82.507890      10/21/2009 10:33:32 PM
025406657      Backhoe      27.982055      -82.508036      10/21/2009 11:58:12 PM
025562152      Car      27.981858      -82.507911      10/21/2009 10:36:55 PM
025562152      Car      27.981891      -82.507800      10/22/2009 3:34:17 PM
025562152      Car      27.981915      -82.507873      10/22/2009 6:02:11 PM
025562152      Car      27.981916      -82.507875      10/22/2009 10:34:19 PM
025799721      Truck      27.981548      -82.507013      10/21/2009 11:04:43 PM
025799721      Truck      27.981778      -82.508350      10/21/2009 11:58:09 PM
025799721      Truck      27.981808      -82.507866      10/22/2009 3:05:47 PM
025799721      Truck      27.981880      -82.507913      10/21/2009 1:39:14 PM
025799721      Truck      27.981931      -82.507851      10/21/2009 10:14:12 PM
025799721      Truck      27.981933      -82.508153      10/22/2009 4:34:19 PM
025799721      Truck      27.981961      -82.507930      10/21/2009 11:16:40 PM
Database Consultant
Top Expert 2009
Commented:
      SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2]
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
   AND  [Event].[2] = (SELECT MAX( [2] ) FROM [Event] WHERE [1] = [GPRSDevice].[GPRSDeviceHardwareID] )
       ORDER BY [Vehicle].[VehicleName]
Rajkumar GsSoftware Engineer

Commented:
Hi Aneesh,

Is it  TOP([2]) to be used here, instead of MAX( [2]) ?

--Raj
AneeshDatabase Consultant
Top Expert 2009

Commented:
RajkumarGS,

No, [2] is his column name , so Max([2] ) returns the max value in that column

Aneesh
SharathData Engineer

Commented:
Can you tell me "Group" in your example. I assume that you want the max [2] for [GPRSDeviceHardwareID] and [VehicleName].
If that is the case, try this. If you still lookng for solution, provide your expected result for the sample set provided.

CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 ;WITH CTE AS 
(
 
       SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2],
            ROW_NUMBER() OVER (PARTITION BY [GPRSDevice].[GPRSDeviceHardwareID],[Vehicle].[VehicleName]
                               ORDER BY [Event].[2] DESC) AS RowNo 
 
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
       
 )
 
 SELECT [GPRSDeviceHardwareID], [VehicleName], [4], [3], [2]
 from CTE 
 where RowNo = 1 
 ORDER BY [VehicleName]
END
GO

Open in new window

Author

Commented:
We're close but "Truck" has a "10/22/2009 4:34:19 PM" which should be the Max[2]


025406657      Backhoe      27.981865      -82.507883      10/23/2009 4:34:19 AM
025562152      Car      27.981916      -82.507875      10/23/2009 4:34:19 AM
025799721      Truck      27.981778      -82.508350      10/22/2009 9:58:09 AM

Author

Commented:
The solution from Sharath_123 results are the same as above.
SharathData Engineer

Commented:
>> The solution from Sharath_123 results are the same as above.
Are you referring ID# 25646824 here ?

>> We're close but "Truck" has a "10/22/2009 4:34:19 PM" which should be the Max[2]
Are you sure, you have a "Truck" record with "10/22/2009 4:34:19 PM" and satisfying you other conditions?

If yes, Can you execute this and provide the output?

CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 ;WITH CTE AS 
(
 
       SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2],
            ROW_NUMBER() OVER (PARTITION BY [GPRSDevice].[GPRSDeviceHardwareID],[Vehicle].[VehicleName]
                               ORDER BY [Event].[2] DESC) AS RowNo 
 
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
       
 )
 
 SELECT [GPRSDeviceHardwareID], [VehicleName], [4], [3], [2],RowNo
 from CTE 
 where [VehicleName] = "Truck" 
 ORDER BY [VehicleName]
END
GO

Open in new window

Author

Commented:
The solution from Sharath_123 results are the same as above.
Are you referring ID# 25646824 here ?    YES

>> We're close but "Truck" has a "10/22/2009 4:34:19 PM" which should be the Max[2]
Are you sure, you have a "Truck" record with "10/22/2009 4:34:19 PM" and satisfying you other conditions?

I double checked and the answer is yes.  Maybe we have to cast the DateTime to make sure that

10/22/2009 4:34:19 PM is read as greater than 10/22/2009 9:58:09 AM ?

These are in GMT and are stored as DateTime.

Author

Commented:
If yes, Can you execute this and provide the output?




025799721      Truck      27.981778      -82.508350      10/22/2009 9:58:09 AM      1
025799721      Truck      27.981778      -82.508350      10/22/2009 8:58:08 AM      2
025799721      Truck      27.981778      -82.508350      10/22/2009 7:58:08 AM      3
025799721      Truck      27.981778      -82.508350      10/22/2009 6:58:08 AM      4
025799721      Truck      27.981933      -82.508153      10/22/2009 4:34:19 PM      5
025799721      Truck      27.981808      -82.507866      10/22/2009 3:05:47 PM      6
025799721      Truck      27.981778      -82.508350      10/22/2009 3:00:20 PM      7
025799721      Truck      27.981778      -82.508350      10/22/2009 2:58:19 PM      8
025799721      Truck      27.981778      -82.508350      10/22/2009 2:58:09 PM      9
025799721      Truck      27.981778      -82.508350      10/22/2009 12:58:10 AM      10
025799721      Truck      27.981778      -82.508350      10/22/2009 12:58:08 PM      11
025799721      Truck      27.981778      -82.508350      10/22/2009 11:58:08 AM      12
025799721      Truck      27.981778      -82.508350      10/22/2009 10:58:08 AM      13
025799721      Truck      27.981778      -82.508350      10/22/2009 1:58:09 PM      14
025799721      Truck      27.981880      -82.507913      10/21/2009 9:14:09 PM      15
025799721      Truck      27.981880      -82.507913      10/21/2009 8:14:09 PM      16
025799721      Truck      27.981880      -82.507913      10/21/2009 7:14:09 PM      17
025799721      Truck      27.981880      -82.507913      10/21/2009 6:14:09 PM      18
025799721      Truck      27.981880      -82.507913      10/21/2009 5:14:10 PM      19
025799721      Truck      27.981880      -82.507913      10/21/2009 4:14:10 PM      20
025799721      Truck      27.981880      -82.507913      10/21/2009 3:14:10 PM      21
025799721      Truck      27.981880      -82.507913      10/21/2009 2:14:10 PM      22
025799721      Truck      27.981778      -82.508350      10/21/2009 11:58:09 PM      23
025799721      Truck      27.981961      -82.507930      10/21/2009 11:24:14 PM      24
025799721      Truck      27.981961      -82.507930      10/21/2009 11:18:01 PM      25
025799721      Truck      27.981961      -82.507930      10/21/2009 11:16:40 PM      26
025799721      Truck      27.981548      -82.507013      10/21/2009 11:15:14 PM      27
025799721      Truck      27.981548      -82.507013      10/21/2009 11:12:13 PM      28
025799721      Truck      27.981548      -82.507013      10/21/2009 11:11:37 PM      29
025799721      Truck      27.981548      -82.507013      10/21/2009 11:10:06 PM      30
025799721      Truck      27.981548      -82.507013      10/21/2009 11:09:28 PM      31
025799721      Truck      27.981548      -82.507013      10/21/2009 11:08:10 PM      32
025799721      Truck      27.981548      -82.507013      10/21/2009 11:06:41 PM      33
025799721      Truck      27.981548      -82.507013      10/21/2009 11:04:43 PM      34
025799721      Truck      27.981931      -82.507851      10/21/2009 11:02:23 PM      35
025799721      Truck      27.981931      -82.507851      10/21/2009 10:59:34 PM      36
025799721      Truck      27.981931      -82.507851      10/21/2009 10:57:41 PM      37
025799721      Truck      27.981931      -82.507851      10/21/2009 10:45:52 PM      38
025799721      Truck      27.981931      -82.507851      10/21/2009 10:39:50 PM      39
025799721      Truck      27.981931      -82.507851      10/21/2009 10:36:55 PM      40
025799721      Truck      27.981931      -82.507851      10/21/2009 10:33:32 PM      41
025799721      Truck      27.981931      -82.507851      10/21/2009 10:32:03 PM      42
025799721      Truck      27.981931      -82.507851      10/21/2009 10:29:08 PM      43
025799721      Truck      27.981931      -82.507851      10/21/2009 10:26:41 PM      44
025799721      Truck      27.981931      -82.507851      10/21/2009 10:14:12 PM      45
025799721      Truck      27.981880      -82.507913      10/21/2009 10:07:40 PM      46
025799721      Truck      27.981880      -82.507913      10/21/2009 10:07:40 PM      47
025799721      Truck      27.981880      -82.507913      10/21/2009 1:44:10 PM      48
025799721      Truck      27.981880      -82.507913      10/21/2009 1:41:50 PM      49
025799721      Truck      27.981880      -82.507913      10/21/2009 1:39:14 PM      50
SharathData Engineer

Commented:
From your resultset, I assume that your [Event].[2] is defined as varchar instead of datetime. Convert this attribute to datetime and try.

CREATE PROCEDURE [dbo].[GetAllLocationsWithVehicleName]
AS
BEGIN
 ;WITH CTE AS 
(
 
       SELECT
            [GPRSDevice].[GPRSDeviceHardwareID],
            [Vehicle].[VehicleName],
            [Event].[4],
            [Event].[3],
            [Event].[2],
            ROW_NUMBER() OVER (PARTITION BY [GPRSDevice].[GPRSDeviceHardwareID],[Vehicle].[VehicleName]
                               ORDER BY CONVERT(datetime,[Event].[2]) DESC) AS RowNo 
 
     FROM  [GPRSDevice], [Vehicle], [Event]
       WHERE [GPRSDevice].[VehicleID] = [Vehicle].[VehicleID]
            AND [GPRSDevice].[GPRSDeviceHardwareID] = [Event].[1]
       
 )
 
 SELECT [GPRSDeviceHardwareID], [VehicleName], [4], [3], [2],RowNo
 from CTE 
 where RowNo = 1
 ORDER BY [VehicleName]
END
GO

Open in new window

Author

Commented:
No - I'm storing it as a DateTime.  The SP above in comment ***239 does not build for me.

Here is the VB.Net code that converts the data stream I get.  I'm pretty sure the loss of leading 0's is the culprit
-------------------------------------------------------------------------------------------------
 Dim EventDate As String = SplitReceived(1)

                EventDate = EventDate.Insert(4, "/")
                EventDate = EventDate.Insert(7, "/")
                EventDate = EventDate.Insert(10, " ")
                EventDate = EventDate.Insert(13, ":")
                EventDate = EventDate.Insert(16, ":")

                Dim FormattedEventDate As DateTime = EventDate
                FormattedEventDate = String.Format("{0:MM/dd/yyyy HH:mm:ss}", FormattedEventDate)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial