Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL: How to get only the latest row

Posted on 2009-05-05
13
Medium Priority
?
388 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
12 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

571 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