|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
need help on sqlI have a table: table1 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO CREATE TABLE [dbo].[Table1] ( [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL , [remark] [ntext] COLLATE Latin1_General_CI_AS NULL , [ldate] [datetime] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO insert into table1 values('001','remarks for 001','2004.05.30') insert into table1 values('001','remarks for 001','2005.05.30') insert into table1 values('002','remarks for 002','2004.04.30') insert into table1 values('003','remarks for 003','2006.05.30') go How can I do a select to return: '001','remarks for 001','2005.05.30' '002','remarks for 002','2004.04.30' '003','remarks for 003','2006.05.30' I am using sql2000 TIA Peter Hi,
You need to do a string concatination in the Select statment to format the result set. Thanks Hari SQL Server MVP Show quoteHide quote "L.Peter" <peter@local.local> wrote in message news:%23qp0pIH2GHA.3908@TK2MSFTNGP05.phx.gbl... > Hi group, > I have a table: table1 > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table [dbo].[Table1] > GO > > CREATE TABLE [dbo].[Table1] ( > [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL , > [remark] [ntext] COLLATE Latin1_General_CI_AS NULL , > [ldate] [datetime] NOT NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > insert into table1 values('001','remarks for 001','2004.05.30') > insert into table1 values('001','remarks for 001','2005.05.30') > insert into table1 values('002','remarks for 002','2004.04.30') > insert into table1 values('003','remarks for 003','2006.05.30') > go > > How can I do a select to return: > '001','remarks for 001','2005.05.30' > '002','remarks for 002','2004.04.30' > '003','remarks for 003','2006.05.30' > I am using sql2000 > TIA > Peter > > Hi Hari,
not so sure of what you meant but here is what I come up with (learnt from Tom reply) select tbl.id,remark,tbl.ldate from (select id,max(ldate) as ldate from table1 group by id) tbl left join table1 on tbl.id= table1.id and table1.ldate = tbl.ldate order by tbl.id Thanks Peter Show quoteHide quote "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:Ono42fH2GHA.4748@TK2MSFTNGP04.phx.gbl... > Hi, > > You need to do a string concatination in the Select statment to format the > result set. > > Thanks > Hari > SQL Server MVP > > "L.Peter" <peter@local.local> wrote in message > news:%23qp0pIH2GHA.3908@TK2MSFTNGP05.phx.gbl... > > Hi group, > > I have a table: table1 > > if exists (select * from dbo.sysobjects where id = > > object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) > > drop table [dbo].[Table1] > > GO > > > > CREATE TABLE [dbo].[Table1] ( > > [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL , > > [remark] [ntext] COLLATE Latin1_General_CI_AS NULL , > > [ldate] [datetime] NOT NULL > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > GO > > insert into table1 values('001','remarks for 001','2004.05.30') > > insert into table1 values('001','remarks for 001','2005.05.30') > > insert into table1 values('002','remarks for 002','2004.04.30') > > insert into table1 values('003','remarks for 003','2006.05.30') > > go > > > > How can I do a select to return: > > '001','remarks for 001','2005.05.30' > > '002','remarks for 002','2004.04.30' > > '003','remarks for 003','2006.05.30' > > I am using sql2000 > > TIA > > Peter > > > > > >  |
|||||||||||||||||||||||
Other interesting topics