Solved

Retrieval of the latest date set of information in SQL database

Posted on 2010-11-09
2
322 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
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 42

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 60
SQL Update Query - What's wrong with this. 18 20
affinity mask in sql server 1 22
Max Consumption Rate (MCR) 3 32
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
A short film showing how OnPage and Connectwise integration works.

919 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

17 Experts available now in Live!

Get 1:1 Help Now