[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Retrieval of the latest date set of information in SQL database

Posted on 2010-11-09
2
Medium Priority
?
327 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 800 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 44

Accepted Solution

by:
zephyr_hex (Megan) earned 1200 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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