CODEWITHSUNDEEP
sqlt-sql
user3676496
user3676496

Reputation: 61

single ID value from multiple rows with ID's

I have a table with values, it has an ID, date time seqno, lat long, event type, and event code with location (Google location).

I am trying to pull data from the table on distinct ID (so only the latest datetime on the ID as value) but it keeps giving me all the rows not only the latest date time.

I have tried to use distinct but it is not working.

I can't ignore any of the data it must be all displayed.

will this then result in using sub queries to get the values as required?

The query I have used is:

SELECT Distinct[Unit_id]
  ,[TransDate]
  ,[event_id]
  ,[EventDesc]
  ,[GeoLocation]
  ,[Latitude]
  ,[Longitude]
  ,[MsgSeqNo]
FROM [bd].[dbo].[table]

the result is the following:

Unit_id TransDate   event_id    EventDesc   GeoLocation Latitude    Longitude   MsgSeqNo
68217   2014-03-10 17:22    345 Battery Disconnect  T3, Ndola, Zambia   -12.96938333    28.62473333 1597114648
68217   2014-03-17 15:01    345 Battery Disconnect  0|Position Invalid  0   0   1602601282
68217   2014-03-18 05:37    345 Battery Disconnect  0|Position Invalid  0   0   1602976336
69181   2014-04-03 11:02    345 Battery Disconnect  Bowe Avenue, Mufulira, Zambia   -12.53835   28.23953333 1616129719
24555   2014-04-21 20:49    345 Battery Disconnect  North Rand, Lethabong, South Africa -26.08998333    28.17598333 1629881440
24555   2014-05-14 06:32    345 Battery Disconnect  17 Fountain Road, Lethabong (Ekurhuleni) 1609, South Africa -26.12955   28.15551667 1646144136
24555   2014-05-14 06:33    345 Battery Disconnect  17 Fountain Road, Edenvale 1609, South Africa   -26.1296    28.15568333 1646146148
24555   2014-05-17 19:37    345 Battery Disconnect  Mokgosi 1, Lobatse, Botswana    -25.2317    25.66831667 1649274858

Upvotes: 0

Views: 60

Answers (3)

Raging Bull
Raging Bull

Reputation: 18737

Use a JOIN:

 SELECT T1.Unit_id, T1.TransDate, T1.event_id, T1.EventDesc, T1.GeoLocation,
        T1.Latitude, T1.Longitude, T1.MsgSeqNo
   FROM [bd].[dbo].[table] T1
        JOIN (SELECT Unit_id, MAX(TransDate) as TransDate
                FROM [bd].[dbo].[table]
               GROUP BY Unit_id) T2
          ON T1.Unit_id = T2.Unit_id AND T1.TransDate = T2.TransDate

Upvotes: 0

Janolof.
Janolof.

Reputation: 141

The DISTINCT keyword will check for distinct row for all specified columns you specify and not the first one directly after. What you must do is partition rows by [Unit_id] and order the partition according to [TransDate]. The follow will help you find the information you are looking for. Note that I have only added three of the columns for simplicity, but it should be easy for you to add the rest of the columns having all column definitions and data.

DECLARE @Data TABLE (
    [Unit_id] INT,
    [TransDate] DATETIME2(0),
    [event_id] INT
)
INSERT INTO
    @Data
VALUES
    ( 68217, '2014-03-10 17:22', 345 ),
    ( 68217, '2014-03-17 15:01', 345 ),
    ( 68217, '2014-03-18 05:37', 345 ),
    ( 69181, '2014-04-03 11:02', 345 ),
    ( 24555, '2014-04-21 20:49', 345 ),
    ( 24555, '2014-05-14 06:32', 345 ),
    ( 24555, '2014-05-14 06:33', 345 ),
    ( 24555, '2014-05-17 19:37', 345 )

;WITH OrderedData AS (
    SELECT
        [Unit_id],
        [TransDate],
        [event_id],
        ROW_NUMBER() OVER (PARTITION BY [Unit_id] ORDER BY [TransDate] DESC) AS [Order]
    FROM
        @Data
)
SELECT
    [Unit_id],
    [TransDate],
    [event_id]
FROM
    OrderedData
WHERE
    [Order] = 1

Note that wham using the WITH keyword you must make sure that there is a statement separator ; between the two statements.

Upvotes: 1

Glenn
Glenn

Reputation: 9150

I'm assuming the Unit_id is unique in the table. But there is probably another unique composite key in the table. I'll assume GeoLocation in which case [GeoLocation, TransDate] might be the unique key. Then you want to find all the records with the max date for the given GeoLocation:

SELECT Unit_id]
  ,[TransDate]
  ,[event_id]
  ,[EventDesc]
  ,[GeoLocation]
  ,[Latitude]
  ,[Longitude]
  ,[MsgSeqNo]
FROM [Ibd].[dbo].[table] x
WHERE TransDate = ( SELECT MAX(TransDate)
                      FROM [Ibd].[dbo].[table]
                      WHERE GeoLocation = x.GeoLocation )

If the unique key is somehting different, then the join needs to be modified accordingly.

Update

Based on sample data and comment:

SELECT Unit_id]
  ,[TransDate]
  ,[event_id]
  ,[EventDesc]
  ,[GeoLocation]
  ,[Latitude]
  ,[Longitude]
  ,[MsgSeqNo]
FROM [Ibd].[dbo].[table] x
WHERE MsgSeqNo= ( SELECT MAX(MsgSeqNo)
                   FROM [Ibd].[dbo].[table]
                   WHERE Unit_id= x.Unit_id)

Just note that using the max sequence does not imply the most recent record, it just implies the highest sequence number associated with the Unit_id. Consider carefully your structure and what you really want.

Upvotes: 1

Related Questions