Re: Query - All from one Table and 'TOP' from another
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: 7 Jan 2007 11:51:00 -0800
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
--
.
- Prev by Date: Re: Listing of all Stored Procedures
- Next by Date: Re: Cannot add the data into the table
- Previous by thread: Re: Listing of all Stored Procedures
- Next by thread: Re: Cannot add the data into the table
- Index(es):
Relevant Pages
|
|
