Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

MS SQL: How to get only the latest row

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
Morten1234
Asked:
Morten1234
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
can u post the table structure and the relations
0
 
tcs224694Commented:
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
 
RiteshShahCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
RiteshShahCommented:
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
 
tcs224694Commented:
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
 
tcs224694Commented:
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
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
ralmadaCommented:
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
 
RiteshShahCommented:
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
 
tcs224694Commented:
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
 
ralmadaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now