|
|
Posted by Erland Sommarskog on 06/14/06 21:45
TGEAR (ted_gear@hotmail.com) writes:
> I have an ItemSTDPriceHistory table as below and this is a child table
> of itemlookup table with one to many relationship.
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[ItemSTDPriceHistory]
> GO
>
>
> CREATE TABLE [dbo].[ItemSTDPriceHistory] (
> [index_id] [int] NOT NULL ,
> [ItemLookUpID] [int] NOT NULL ,
> [dtbegin] [datetime] NOT NULL ,
> [price] [decimal](18, 0) NOT NULL ,
> ) ON [PRIMARY]
> GO
This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
> I try to get the output of the most latest date for each ItemLookUpID
SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate
> these are sample records. (I am sorry I do not know how to write a sql
> statment with records in it)
You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:
INSERT tbl(col1, col2, col3, ...)
(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)
The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:
VALUES (val1, val2, val3, ...)
The values can be expressions, but cannot include queries.
That's the basics. Now you can practice. :-) When in doubt consult Books
Online.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|