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
 Select Data from table

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 all
I have a select query which produces data as follows

Col1------Col2------Col3------Col4
A------MAN201201232321------T------Accepted
A------MAN201001232331------Y------Rejected
B------MAN201301019345------Y------Hold
B------MAN201302129333------T------Hold
B------MAN201212319999------T------Accepted
C------MAN201112319933------Y------Accepted
D------MAN201011229988------Y------Hold
D------MAN200911129239------Y------Rejected
D------MAN200610309999------Y------Accepted

Here is the data which my select queery produces. What I want to do is to fetch the oldest records only based on column 2 criteria

The data present in Col2 is like MANYYYYMMDDXXXX. Just do on the basis of YYYYMMDD structure only

The output required is

Col1------Col2------Col3------Col4
A------MAN201001232331------Y------Rejected
B------MAN201212319999------T------Accepted
C------MAN201112319933------Y------Accepted
D------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)dt
where rn=1


Too old to Rock'n'Roll too young to die.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-11 : 10:05:29
Which *?
The * in outer select or inner select statement
Go to Top of Page

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.
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 02:16:30
Hello, There is some change in requirement..

Col1------Col2------Col3------Col4------Col5
A------MAN201201232321------T------Accepted------2008-10-17 00:00:00.000
A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000
B------MAN201301019345------Y------Hold------2009-11-22 00:00:00.000
B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000
B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000
C------MAN201112319933------Y------Accepted------2012-05-30 00:00:00.000
D------MAN201011229988------Y------Hold------2003-10-10 00:00:00.000
D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000
D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000

Here is the data which my select queery produces. What I want to do is to fetch the newest records only based on column 5 criteria

The data present in Col5 is like 2008-10-17 00:00:00.000. Just do on the basis of YYYYMMDD structure only

The output required is

Col1------Col2------Col3------Col4------Col5
A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000
B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000
B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000
D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000
D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 02:26:51
Check this...

SELECT Col1, Col2, Col3, Col4, Col5
FROM ( SELECT *, ROW_NUMBER() OVER( PARTITION BY Col1 ORDER BY CAST(Col5 AS DATE))Seq FROM TableName)
WHERE Seq>1

--
Chandu
Go to Top of Page

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, DCN

Is it your result of current query?
Col1------Col2------Col3------Col4------Col5
A------MAN201201232321------T------Accepted------2008-10-17 00:00:00.000
A------MAN201001232331------Y------Rejected------2009-11-17 00:00:00.000
B------MAN201301019345------Y------Hold------2009-11-22 00:00:00.000
B------MAN201302129333------T------Hold------2010-10-21 00:00:00.000
B------MAN201212319999------T------Accepted------2011-11-17 00:00:00.000
C------MAN201112319933------Y------Accepted------2012-05-30 00:00:00.000
D------MAN201011229988------Y------Hold------2003-10-10 00:00:00.000
D------MAN200911129239------Y------Rejected------2004-11-12 00:00:00.000
D------MAN200610309999------Y------Accepted------2004-11-17 00:00:00.000



--
Chandu
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 04:31:02
Any IDEA, Can you please merge the changes
Go to Top of Page

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_Date
FROM CTE
WHERE Seq >1
[/code]


--
Chandu
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 04:49:23
The Error I am getting is
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 04:50:37
Solved Indtead of DATE I wrote DATETIME..and it works
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2013-03-12 : 09:28:37
Yes.. Thanks for helping
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -