Solved

Retrieval of the latest date set of information in SQL database

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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