getting oldest ddsdate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting oldest ddsdate

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-23 : 14:48:59

How do I get the oldest date?

This didn't work:

select fo, doc, clms, cos, filedate, min(ddsrcpt) as mindate, title
from test
where clms='215478'
group by fo, doc, clms, cos, filedate, title

I want the record with mindate of 12/23/2010 to be returned.

CREATE TABLE [dbo].[test](
[fo] [varchar](3) NULL,
[doc] [varchar](4) NULL,
[clms] [char] (6) NULL,
[cos] [char] (6) NULL,
[FileDate] [char](30) NULL,
[DDSRcpt] [char](30) NULL,
[title] [varchar](3) NULL
) ON [PRIMARY]

insert into test
select '268', 'S09', '2154789', '2154789', '12/22/2010', '11/10/2011', 'T16' union all
select '268', 'S09', '2154789', '2154789', '12/22/2010', '12/23/2010', 'T12'

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-23 : 15:01:56

I tried this but still not getting the oldest date which is 12/23/2010

select fo, doc, clms, cos, filedate, ddsrcpt, title
from
(select *, row_number() over (Partition by clms order by ddsrcpt) as rownum
from test) p
where clms='215478' and p.rownum= 1
group by fo, doc, clms, cos, filedate, ddsrcpt, title

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-23 : 15:09:14

Order by clause should work for you - for example this:


Select top 1 * from dbo.Test
order by cast(ddsrcpt as datetime) DESC

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-11-23 : 16:04:48

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources