SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Data from table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goodman2253
Yak Posting Veteran

83 Posts
Posted - 03/11/2013 :  09:05:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 03/11/2013 :  09:29:41  Show Profile  Visit webfred's Homepage  Reply with Quote
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

83 Posts
Posted - 03/11/2013 :  09:48:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 03/11/2013 :  09:52:12  Show Profile  Visit webfred's Homepage  Reply with Quote
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

83 Posts
Posted - 03/11/2013 :  10:05:29  Show Profile  Reply with Quote
Which *?
The * in outer select or inner select statement
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 03/11/2013 :  10:43:08  Show Profile  Visit webfred's Homepage  Reply with Quote
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

83 Posts
Posted - 03/12/2013 :  02:16:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  02:26:51  Show Profile  Reply with Quote
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
Edited by - bandi on 03/12/2013 02:28:36
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  03:29:38  Show Profile  Reply with Quote
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

83 Posts
Posted - 03/12/2013 :  04:31:02  Show Profile  Reply with Quote
Any IDEA, Can you please merge the changes
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  04:44:09  Show Profile  Reply with Quote 
;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



--
Chandu
Go to Top of Page

goodman2253
Yak Posting Veteran

83 Posts
Posted - 03/12/2013 :  04:49:23  Show Profile  Reply with Quote
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

83 Posts
Posted - 03/12/2013 :  04:50:37  Show Profile  Reply with Quote
Solved Indtead of DATE I wrote DATETIME..and it works
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 03/12/2013 :  05:13:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  05:26:41  Show Profile  Reply with Quote
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

83 Posts
Posted - 03/12/2013 :  09:28:37  Show Profile  Reply with Quote
Yes.. Thanks for helping
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  09:35:02  Show Profile  Reply with Quote
quote:
Originally posted by goodman2253

Yes.. Thanks for helping

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000