Solved

Retrieval of the latest date set of information in SQL database

Posted on 2010-11-09
2
325 Views
Last Modified: 2013-11-05
Hi, I am creating a Crystal report and I need to select information between two tables.

One table holds all the data items (TableA) that I want to report on. I then need to retrieve Audit history from another table (tableB).

In my cystal report I want to compare beside each other the new value and the last history item.
The problem is that I get many rows of the same data with the last part of the tableB showing all the times the values have been changed.

I would like to create a select statement that would choose the latest time stamped item, so that only one row show for each of the tableA items. The problem is that the TableA may be null or an item or it could have 1 to many values.

What is the best way to only select the last value from table B for each of Table A items? I am sure there are key words for this but I haven't done SQL select statments for a few years now.
0
Comment
Question by:mumbles22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Assisted Solution

by:skrombeen
skrombeen earned 200 total points
ID: 34094410
HI

you need to do this on your join:
select * from tablea inner join tableb on tablea.PrimaryID = tableb.fkPrimaryKey
and Tableb.PrimaryKey = (select top 1 primarykey from tableb where fkPrimaryKey = tablea.PrimaryKey order by Tableb.date desc)
0
 
LVL 43

Accepted Solution

by:
zephyr_hex (Megan) earned 300 total points
ID: 34094874
you can use max()

there are different ways you can do this.  one is with a subquery:

select table1.field1, (select max(table2.timestamp) from table2 where table2.keyvalue = table1.keyvalue), table1.field2, table1.field3
from table1

this will give you your data from table1, along with the max timestamp associated with the item from table2.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

705 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