Solved

MS SQL: How to get only the latest row

Posted on 2009-05-05
13
334 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now