Solved

Retrieval of the latest date set of information in SQL database

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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