Solved

MS SQL: How to get only the latest row

Posted on 2009-05-05
13
349 Views
Last Modified: 2012-05-06
I want to extract the latest entry for each object (Product) in the database, but I don't know how to code this in MS SQL.
I have prepared an example (below). I wish to return 4 fields from different 3 tables, but for each product (Product_ID) only the row with the latest timestamp. I have attached the example data model in .xls format and included my SQL query in the Code section.

Desired result:
ACCOUNT_ID / ACCOUNT_STATUS / ENTITY_TIMESTAMP / PROP_OLD_VALUE
1      Closed      04/05/2009 14:00      Reopened
2      Closed      05/05/2009 12:01      New
3      Reopened      05/05/2009 17:43      Closed

Actual result (with my own SQL statement, see Code section):                  
1      Closed      22/04/2009 13:30      (Null)
1      Closed      23/04/2009 08:00      New
1      Closed      02/05/2009 15:00      Closed
1      Closed      04/05/2009 14:00      Reopened
2      Closed      22/04/2009 11:07      (Null)
2      Closed      05/05/2009 12:01      New
3      Reopened      21/04/2009 09:55      (Null)
3      Reopened      22/04/2009 16:29      New
3      Reopened      05/05/2009 17:43      Closed

I have unsuccessfully tried to implement MAX(ENTITY_TIMESTAMP) or TOP 1 in the query. I almost got it when I combined MAX(ENTITY_TIMESTAMP) and GROUP BY ACCOUNT_ID , but then I was not able to get all 4 fields...

I would be grateful for any help :-)

select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
      A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID

Open in new window

How-to-get-only-the-latest-row.xls
0
Comment
Question by:Morten1234
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24304281
can u post the table structure and the relations
0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24304354
Did u try this?
select
       A. ACCOUNT_ID,
       max(A. ACCOUNT_STATUS),
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
      A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID group by A. ACCOUNT_STATUS

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24304361
you may be looking for



select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
      a.entity_timestamp=(select max(entity_timestamp) from Event_Log)

Open in new window

0
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.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24304366
sorry typo in above.

select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
      el.entity_timestamp=(select max(entity_timestamp) from Event_Log)
0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24304368
Sorry that is weird...Try this..
select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       max(EL. ENTITY_TIMESTAMP),
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
      A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID group by EL. ENTITY_TIMESTAMP

Open in new window

0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24304415
Ritiesh i jus see the excel...I think will work...Am i right?

select max(entity_timestamp) from Event_Log group by ENTITY_ID;


0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24304422
tcs224694,

group by will be heavy for this task, don't you think so it should be done with just one WHERE clause?

BTW, author can add more condition in WHERE clause


0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24304432
like this:


select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
       A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID
      and el.entity_timestamp=(select max(entity_timestamp) from Event_Log)
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 24304598
I would do it like this:
select
       A.ACCOUNT_ID,
       A.ACCOUNT_STATUS,
       EL.ENTITY_TIMESTAMP,
       EP.PROP_OLD_VALUE
 
from Account A
INNER JOIN Event_Log EL on A.ACCOUNT_ID = EL.ENTITY_ID
INNER JOIN Event_Properties EP ON EL.ACTION_ID = EP.ACTION_ID
where el.entity_timestamp=(select max(entity_timestamp) from Event_Log where ENTITY_ID = EL.Entity_ID)

Open in new window

0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 total points
ID: 24304651
Hi Ralmada,

I guess author wants latest row in table without any condition otherwise my query could be tweaked as below:



select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
       A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID
      and el.entity_timestamp=(select max(entity_timestamp) from Event_Log where Entity_ID=EL.EntityID)

Open in new window

0
 
LVL 2

Expert Comment

by:tcs224694
ID: 24304676
RiteshShah,

Sorry for the delay i am not getting any mail alert i dont know why....

What u did is right,but in the where clause u missing the groupby ...Does it work without the groupby...

Since the where clause get max timestamp of only 1 record only...But he needs for every entity id the latest timestamp.So i think this is what he is looking for...Please post ur comments...

select
       A. ACCOUNT_ID,
       A. ACCOUNT_STATUS,
       EL. ENTITY_TIMESTAMP,
       EP. PROP_OLD_VALUE
 
     from Account A, Event_Log EL, Event_Properties EP
 
     where
       A. ACCOUNT_ID = EL_ ENTITY_ID
      and EL_ACTION_ID = EP_ACTION_ID
      and el.entity_timestamp=(select max(entity_timestamp) from Event_Log group by ENTITY_ID)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24305594
Ritesh, your last comment does the same thing as mine. Also, I recommend using ANSI standards  for joining tables (INNER JOIN). Not only  I found them more clear, but is considered best practice as well.
I still feel the asker wants not just the last row of the table, but the last event on each ID. that's why I suggested this in my comment 24304598.

select
       A.ACCOUNT_ID,
       A.ACCOUNT_STATUS,
       EL.ENTITY_TIMESTAMP,
       EP.PROP_OLD_VALUE
 
from Account A
INNER JOIN Event_Log EL on A.ACCOUNT_ID = EL.ENTITY_ID
INNER JOIN Event_Properties EP ON EL.ACTION_ID = EP.ACTION_ID
where el.entity_timestamp=(select max(entity_timestamp) from Event_Log where ENTITY_ID = EL.Entity_ID)

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

806 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