Query - All from one Table and 'TOP' from another

Hi - Have query structure language question
Hi have two tables,
Table A has a picklist for a field of limited options - say two, X, Y or Z
Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
but other data is entered, such as a date. So over time there will be many
entries with the linked field being either X, Y or Z, but with a timeline of
dates.
Want I want is a query that will return results for every item in the
picklist (in this case three, X,Y or Z and only the top value (most recent
date) for the date field in Table B.
(FYI - another Table C, links to table A where other fields must be equal.
Thus, what I hope to achieve is that, an entry in the Table C will be
assigned a link to table A, which will inturn return the most up to date
value in Table B.)
Hope someone can help. Ask if you need more detail.
Jon
Jonathan wrote:
> Hi - Have query structure language question
Unfortunately you didn't post any SQL. Instead you posted a word
puzzle. So we have to work out what you meant before we can answer you.
Posting some simple CREATE TABLE statements is a much better way to
describe a problem and it would have saved you some typing too :-)

> Hi have two tables,
> Table A has a picklist for a field of limited options - say two, X, Y or Z
> Table B 'looks up' to those values, so new entrys, must be either X, Y or Z,
> but other data is entered, such as a date. So over time there will be many
> entries with the linked field being either X, Y or Z, but with a timeline of
> dates.
> Want I want is a query that will return results for every item in the
> picklist (in this case three, X,Y or Z and only the top value (most recent
> date) for the date field in Table B.
> (FYI - another Table C, links to table A where other fields must be equal.
> Thus, what I hope to achieve is that, an entry in the Table C will be
> assigned a link to table A, which will inturn return the most up to date
> value in Table B.)
>
Here's my guess:
CREATE TABLE B (x INT NOT NULL, y INT NOT NULL, z INT NOT NULL, dt
DATETIME NOT NULL, PRIMARY KEY (x,y,z,dt /* ? */));
/* Latest date for each x,y,z */
SELECT x,y,z, MAX(dt) AS dt
FROM B
GROUP BY x,y,z ;

> Hope someone can help. Ask if you need more detail.
DDL with keys and other constraints. Sample data (INSERT statements)
and show what end result you want based on that sample data. Also tell
us what version of SQL Server you have.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx