Need to get oldest filedate - 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

Need to get oldest filedate

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-06-06 : 14:02:31

How do I get the oldest date:

This query gives me both dates for clms '549874'

Select area, dist, doc, reg, clms, [type], convert(char,flg_cdt, 101) as flg_cdt, Location
FROM pc1
Where type = '3' and doc = 'c09'
order by clms

How do I get the date of 8/12/2010 just to appear for clms '549874'?

CREATE TABLE [dbo].[PC1](
[area] [varchar](2) NULL,
[dist] [char](3) NULL,
[doc] [char](3) NOT NULL,
[reg] [char](1) NULL,
[clms] [char](6) NOT NULL,
[type] [varchar](1) NOT NULL,
[flg_cdt] [datetime] NULL,
[Location] [varchar] (50) NOT NULL
) ON [PRIMARY]

GO
Insert into pc1

Select '02', '199', 'C09', 'c', '549874', '3', '08/12/2010', 'FO' union all
Select '02', '199', 'C09', 'c', '549874', '3', '09/15/2010', 'DS' union all
Select '02', '199', 'C09', 'c', '589632', '3', '08/25/2010', 'FO' union all
Select '02', '199', 'C09', 'c', '152698', '3', '09/20/2010', 'FO' union all
Select '02', '216', 'C09', 'c', '012598', '2', '09/18/2010', 'FO'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-06 : 14:30:52

If you are on SQL 2005 or higher, you can use the row_number() function like this:


Select area, dist, doc, reg, clms, [type], convert(char,flg_cdt, 101) as flg_cdt, Location
FROM 
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY clms ORDER BY flg_cdt) AS rownum FROM pc1
)p
Where type = '3' and doc = 'c09' AND p.rownum = 1
order by clms

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-06-06 : 14:59:39

Thanks I tried the row_num before but I had the partition by flg_cdt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources