Author |
Topic |
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-11 : 09:05:57
|
Hello, How All are doing today. I have a issue which I want to share with allI have a select query which produces data as follows Col1------Col2------Col3------Col4A------MAN201201232321------T------AcceptedA------MAN201001232331------Y------RejectedB------MAN201301019345------Y------HoldB------MAN201302129333------T------HoldB------MAN201212319999------T------AcceptedC------MAN201112319933------Y------AcceptedD------MAN201011229988------Y------HoldD------MAN200911129239------Y------RejectedD------MAN200610309999------Y------AcceptedHere is the data which my select queery produces. What I want to do is to fetch the oldest records only based on column 2 criteriaThe data present in Col2 is like MANYYYYMMDDXXXX. Just do on the basis of YYYYMMDD structure onlyThe output required is Col1------Col2------Col3------Col4A------MAN201001232331------Y------RejectedB------MAN201212319999------T------AcceptedC------MAN201112319933------Y------AcceptedD------MAN200610309999------Y------Accepted |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-11 : 09:29:41
|
select * from(select row_number() over (partition by col1 order by convert(date(substring(col2,4,8)) asc) rn,* from yourtable)dtwhere rn=1 Too old to Rock'n'Roll too young to die. |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-11 : 09:48:24
|
like I a writing Select Col1, Col2, Col3, Col4 from (select .........)Then How I will embedd my sql query to your sql query |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-11 : 09:52:12
|
just replace the first * with your col1, col2, col3, col4 and leave the rest as it is... Too old to Rock'n'Roll too young to die. |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-11 : 10:05:29
|
Which *?The * in outer select or inner select statement |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-11 : 10:43:08
|
what do you think what I mean with "the first *"?The * in the outer select! Too old to Rock'n'Roll too young to die. |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-12 : 02:16:30
|
Hello, There is some change in requirement..Col1------Col2------Col3------Col4------Col5A------MAN201201232321------T------Accepted------2008-10-17 00:00:00.000A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000B------MAN201301019345------Y------Hold------2009-11-22 00:00:00.000B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000C------MAN201112319933------Y------Accepted------2012-05-30 00:00:00.000D------MAN201011229988------Y------Hold------2003-10-10 00:00:00.000D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000Here is the data which my select queery produces. What I want to do is to fetch the newest records only based on column 5 criteriaThe data present in Col5 is like 2008-10-17 00:00:00.000. Just do on the basis of YYYYMMDD structure onlyThe output required is Col1------Col2------Col3------Col4------Col5A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 02:26:51
|
Check this...SELECT Col1, Col2, Col3, Col4, Col5FROM ( SELECT *, ROW_NUMBER() OVER( PARTITION BY Col1 ORDER BY CAST(Col5 AS DATE))Seq FROM TableName)WHERE Seq>1--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 03:29:38
|
Whats your DATETIME column?Member_No, Document_Id, Description, Type, Status, Region, Queue, DCNIs it your result of current query?Col1------Col2------Col3------Col4------Col5A------MAN201201232321------T------Accepted------2008-10-17 00:00:00.000A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000B------MAN201301019345------Y------Hold------2009-11-22 00:00:00.000B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000C------MAN201112319933------Y------Accepted------2012-05-30 00:00:00.000D------MAN201011229988------Y------Hold------2003-10-10 00:00:00.000D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000--Chandu |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-12 : 04:31:02
|
Any IDEA, Can you please merge the changes |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 04:44:09
|
[code];With CTE AS( SELECT Member_No, Document_Id, Description, Type, Status, Region, Queue, DCN, Aging_Date ,ROW_NUMBER() OVER( PARTITION BY Member_No ORDER BY CAST(Aging_Date AS DATE))Seq FROM ( SELECT folder_properties.property_value AS Member_No, ......Your Query)SELECT Member_No, Document_Id, Description, Type, Status, Region, Queue, Aging_DateFROM CTEWHERE Seq >1[/code]--Chandu |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-12 : 04:49:23
|
The Error I am getting isMsg 243, Level 16, State 1, Line 1Type DATE is not a defined system type |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-12 : 04:50:37
|
Solved Indtead of DATE I wrote DATETIME..and it works |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 05:13:04
|
quote: Originally posted by goodman2253 Solved Indtead of DATE I wrote DATETIME..and it works
DATE is available only from SQL 2008 onwards------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 05:26:41
|
quote: Originally posted by goodman2253 Solved Indtead of DATE I wrote DATETIME..and it works
Did you get correct result?--Chandu |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 2013-03-12 : 09:28:37
|
Yes.. Thanks for helping |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 09:35:02
|
quote: Originally posted by goodman2253 Yes.. Thanks for helping
Welcome--Chandu |
|
|
|