sql server mseq: 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.

[quoted text, click to view]

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 :-)

[quoted text, click to view]

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 ;

[quoted text, click to view]

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
--