Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Need help on SQL Query

Answered Need help on SQL Query

  • Thursday, February 07, 2013 5:34 AM
     
     
    Sign In to Vote
    0

    Hello,

    Hope all of you are doing alright. I have my database table looking like as below

    Col1 Col2    Col3
    /sites/site1/Shared+Documents/Forms/AllItems.aspx 2/6/2013    User1
    /sites/site1/default.aspx 2/7/2013    User2
    /sites/site1/Shared+Documents 2/8/2013    User3
    /sites/site2/Shared+Documents/Forms/AllItems.aspx 2/9/2013    User4
    /sites/site2/default.aspx 2/10/2013    User5
    /sites/site2/Shared+Documents 2/11/2013    User6
    /sites/site3/Shared+Documents/Forms/AllItems.aspx 2/12/2013    User7
    /sites/site3/default.aspx 2/20/2013    User8
    /sites/site3/Shared+Documents 2/14/2013    User9
    /sites/site4/Shared+Documents/Forms/AllItems.aspx 2/17/2013    User10
    /sites/site4/default.aspx 2/16/2013    User11
    /sites/site4/Shared+Documents 2/15/2013    User12

    and I need OUTPUT  as below :

    OutPut :
    /sites/site1/Shared+Documents 2/8/2013     User3
    /sites/site2/Shared+Documents 2/11/2013     User6
    /sites/site3/default.aspx 2/20/2013     User8
    /sites/site4/Shared+Documents/Forms/AllItems.aspx 2/17/2013     User10

    What I need actually is - as you can see /sites/site1 has 3 entries in the DB table in col1. I need the latest date entry for sites/site1...So output would be for site1

    /sites/site1/Shared+Documents      2/8/2013   User3

    the same logic is applied to site2, site3 and site4.

    Can you please help me with SQL query?

    Thanks.

     

All Replies

  • Thursday, February 07, 2013 6:08 AM
     
      Has Code
    Sign In to Vote
    1

    try this

    declare @tab table(Col1	varchar(100),Col2	   date,Col3 varchar(10));
insert into @tab values
('/sites/site1/Shared+Documents/Forms/AllItems.aspx',	'2/6/2013'	   ,'User1'),
('/sites/site1/default.aspx',	'2/7/2013'	   ,'User2'),
('/sites/site1/Shared+Documents',	'2/8/2013'	   ,'User3'),
('/sites/site2/Shared+Documents/Forms/AllItems.aspx',	'2/9/2013'	   ,'User4'),
('/sites/site2/default.aspx',	'2/10/2013'	   ,'User5'),
('/sites/site2/Shared+Documents',	'2/11/2013'	   ,'User6'),
('/sites/site3/Shared+Documents/Forms/AllItems.aspx',	'2/12/2013'	   ,'User7'),
('/sites/site3/default.aspx',	'2/20/2013'	   ,'User8'),
('/sites/site3/Shared+Documents',	'2/14/2013'	   ,'User9'),
('/sites/site4/Shared+Documents/Forms/AllItems.aspx',	'2/17/2013'	   ,'User10'),
('/sites/site4/default.aspx',	'2/16/2013'	   ,'User11'),
('/sites/site4/Shared+Documents',	'2/15/2013'	   ,'User12');

with cte as(
select *,SUBSTRING(col1, case when left(col1,1)='/' then  CHARINDEX('/',Col1,2)+1 else 1 end,5) SiteName from @tab),
CTE_final as 
(select *, ROW_NUMBER() OVER(partition by SiteName ORder by col2 desc) RN from CTE)
select * from CTE_final
where RN=1

    Satheesh

     
  • Monday, February 11, 2013 5:18 PM
     
     
    Sign In to Vote
    0

    Hi Sathesh,

    Thanks for your help and really you seem expert on writing complex SQL Query. The above query just perfectly working fine but I need your little help in modifying a bit as I’m not SQL expert

    Here is few thing I would like to do.

    1. I already have the data in the database which looks like exactly per the given example. So I believe I don’t need below part of the query
    • declare @tab table(Col1                                           varchar(100),Col2         date,Col3 varchar(10));
    • insert into @tab values
    • ('/sites/site1/Shared+Documents/Forms/AllItems.aspx',             '2/6/2013'        ,'User1'),
    • ('/sites/site1/default.aspx',                                     '2/7/2013'        ,'User2'),
    • ('/sites/site1/Shared+Documents',                                 '2/8/2013'        ,'User3'),
    • ('/sites/site2/Shared+Documents/Forms/AllItems.aspx',             '2/9/2013'        ,'User4'),
    • ('/sites/site2/default.aspx',                                     '2/10/2013'       ,'User5'),
    • ('/sites/site2/Shared+Documents',                                 '2/11/2013'       ,'User6'),
    • ('/sites/site3/Shared+Documents/Forms/AllItems.aspx',             '2/12/2013'       ,'User7'),
    • ('/sites/site3/default.aspx',                                     '2/20/2013'       ,'User8'),
    • ('/sites/site3/Shared+Documents',                                 '2/14/2013'       ,'User9'),
    • ('/sites/site4/Shared+Documents/Forms/AllItems.aspx',             '2/17/2013'       ,'User10'),
    • ('/sites/site4/default.aspx',                                     '2/16/2013'       ,'User11'),
    • ('/sites/site4/Shared+Documents',                                 '2/15/2013'       ,'User12');

    2.SiteName column (Site1, site2, site2,site4) are NOT just 5 characters. It can be N no of characters. So can you please help me in modifying your query accordingly?

    3.The Col1 output looks like as below as of now

    /sites/site1/Shared+Documents

    /sites/site2/Shared+Documents

    /sites/site3/default.aspx

    /sites/site4/Shared+Documents/Forms/AllItems.aspx

    Can we just modify the query to look like as below

    /sites/site1/

    /sites/site2/

    /sites/site3/

    /sites/site4/

    I’m sorry It’s my mistake that I didn’t mention in my initial post.

    4.In my initial post, I have mentioned that col2 would be just date. I’ sorry I was wrong. It’s date and time and actually it looks like as below as an example…

    2012-08-16 00:00:00.000

    2012-08-16 00:00:01.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:06.000

    2012-08-16 00:00:09.000

    2012-08-16 00:00:10.000

    2012-08-16 00:00:10.000

    2012-08-16 00:00:10.000

    2012-08-16 00:00:17.000

    2012-08-16 00:00:21.000

    2012-08-16 00:00:30.000

    2012-08-16 00:00:32.000

    2012-08-16 00:00:36.000

    2012-08-16 00:00:44.000

    2012-08-16 00:00:45.000

    2012-08-16 00:00:30.000

    So when we sort with latest entry for each site, it should be sorted per date as well as time. I’m sorry this is also new requirement.

    5.Now Once we have the query ready as per above 4 points, I need to run it on the result of my below query.

    SELECT

        [Col1]

        ,[Col2]

        ,[Col3]

       

      FROM [DBName].[dbo].[TableName]

      where Col1 like '%/sites/%'

            and HttpStatus like '200'

            and Col3 not like 'XYZ\UserA'

            and Col3 not like 'XYZ\userB'

    All your help is appreciated.

    Thanks.


    • Edited by abc67 Monday, February 11, 2013 5:20 PM
    •  
     
  • Tuesday, February 12, 2013 5:46 AM
     
     Answered Has Code
    Sign In to Vote
    1

    I hope this one would work for you

    declare @tab table(Col1	varchar(100),Col2	   datetime,Col3 varchar(10));
insert into @tab values
('/sites/site1/Shared+Documents/Forms/AllItems.aspx',	'2/6/2013'	   ,'User1'),
('/sites/site1/default.aspx',	'2/7/2013'	   ,'User2'),
('/sites/site1/Shared+Documents',	'2/8/2013'	   ,'User3'),
('/sites/site2/Shared+Documents/Forms/AllItems.aspx',	'2/9/2013'	   ,'User4'),
('/sites/site2/default.aspx',	'2/10/2013'	   ,'User5'),
('/sites/site2/Shared+Documents',	'2/11/2013'	   ,'User6'),
('/sites/site3/Shared+Documents/Forms/AllItems.aspx',	'2/12/2013'	   ,'User7'),
('/sites/site3/default.aspx',	'2/20/2013'	   ,'User8'),
('/sites/site3/Shared+Documents',	'2/14/2013'	   ,'User9'),
('/sites/site4/Shared+Documents/Forms/AllItems.aspx',	'2/17/2013'	   ,'User10'),
('/sites/site4/default.aspx',	'2/16/2013'	   ,'User11'),
('/sites/site4/Shared+Documents',	'2/15/2013'	   ,'User12');

with cte as(
select *,SUBSTRING(col1,1,CHARINDEX('/',Col1, CHARINDEX('/',Col1,2)+1)) SiteName from @tab)
--select * from cte
,
CTE_final as 
(select *, ROW_NUMBER() OVER(partition by SiteName Order by col2 desc) RN from CTE)
select * from CTE_final
where RN=1

    Satheesh

    • Proposed As Answer by SQL_Learn Tuesday, February 12, 2013 11:17 AM
    • Marked As Answer by Iric WenModerator Monday, February 18, 2013 6:28 AM
    •