none
SQL ROW_NUMBER with Joining

    Question

  • I need to use ROW_NUMBER() in the following Query - 

                                  

    CREATE proc SProc_SelectPhotographersByMainCategory        
    @category varchar(500)      
    as        
    begin      


    DECLARE @TEST varchar(1000)      
    DECLARE @SQLQuery AS NVARCHAR(1000)      

    SET @TEST = @category      

    SET @SQLQuery = 'select distinct ContributerSubCategoryMapping.ContributorID,  PhotographerContributors_tbl.*  from ContributerSubCategoryMapping           
      left outer join PhotographerContributors_tbl on PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID          
      left outer join tbl_City on tbl_City.CityID=ContributerSubCategoryMapping.LocationId          
      where     
      ContributerSubCategoryMapping.Subcatid IN('+ @category +') and PhotographerContributors_tbl.IsActive=''1'''      
    EXECUTE(@SQLQuery)      

    end

    Please Help :(


    Friday, August 29, 2014 10:16 AM

Answers

  • Try this one,

    CREATE proc SProc_SelectPhotographersByMainCategory        
     @category varchar(500)      
     as        
     begin      
     
    
    DECLARE @TEST varchar(1000)      
     DECLARE @SQLQuery AS NVARCHAR(1000)      
     
    SET @TEST = @category      
     
    SET @SQLQuery = 'select *, row_number() over (order by ContributorID1) as RowNumber from (select distinct 
    ContributerSubCategoryMapping.ContributorID as ContributorID1,  
    PhotographerContributors_tbl.*  
    from ContributerSubCategoryMapping           
       left outer join PhotographerContributors_tbl on 
    			PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID          
       left outer join tbl_City on 
    			tbl_City.CityID=ContributerSubCategoryMapping.LocationId          
       where     
       ContributerSubCategoryMapping.Subcatid IN('+ @category +') and PhotographerContributors_tbl.IsActive=''1'') x'      
     EXECUTE(@SQLQuery)      
     
    end
    


    Regards, RSingh

    • Marked as answer by rajeev0011 Friday, August 29, 2014 10:51 AM
    Friday, August 29, 2014 10:49 AM

All replies

  • And in which way do you want to use ROW_NUMBER here? In which order / with which criteria should the row_number be generated?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 10:22 AM
  • with order by ContributerSubCategoryMapping.ContributorID 
    Friday, August 29, 2014 10:26 AM
  • Try below query,

    CREATE proc SProc_SelectPhotographersByMainCategory        
     @category varchar(500)      
     as        
     begin      
     
    
    DECLARE @TEST varchar(1000)      
     DECLARE @SQLQuery AS NVARCHAR(1000)      
     
    SET @TEST = @category      
     
    SET @SQLQuery = 'select *, row_number() over (order by ContributorID) as RowNumber from (select distinct 
    ContributerSubCategoryMapping.ContributorID,  
    PhotographerContributors_tbl.*  
    from ContributerSubCategoryMapping           
       left outer join PhotographerContributors_tbl on 
    			PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID          
       left outer join tbl_City on 
    			tbl_City.CityID=ContributerSubCategoryMapping.LocationId          
       where     
       ContributerSubCategoryMapping.Subcatid IN('+ @category +') and PhotographerContributors_tbl.IsActive=''1'') x'      
     EXECUTE(@SQLQuery)      
     
    end


    Regards, RSingh

    Friday, August 29, 2014 10:30 AM
  • Thanks for the reply i used to your query but getting the  error -

    Msg 8156, Level 16, State 1, Line 4

    The column 'ContributorId' was specified multiple times for 'x'.

    Friday, August 29, 2014 10:42 AM
  • Try this one,

    CREATE proc SProc_SelectPhotographersByMainCategory        
     @category varchar(500)      
     as        
     begin      
     
    
    DECLARE @TEST varchar(1000)      
     DECLARE @SQLQuery AS NVARCHAR(1000)      
     
    SET @TEST = @category      
     
    SET @SQLQuery = 'select *, row_number() over (order by ContributorID1) as RowNumber from (select distinct 
    ContributerSubCategoryMapping.ContributorID as ContributorID1,  
    PhotographerContributors_tbl.*  
    from ContributerSubCategoryMapping           
       left outer join PhotographerContributors_tbl on 
    			PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID          
       left outer join tbl_City on 
    			tbl_City.CityID=ContributerSubCategoryMapping.LocationId          
       where     
       ContributerSubCategoryMapping.Subcatid IN('+ @category +') and PhotographerContributors_tbl.IsActive=''1'') x'      
     EXECUTE(@SQLQuery)      
     
    end
    


    Regards, RSingh

    • Marked as answer by rajeev0011 Friday, August 29, 2014 10:51 AM
    Friday, August 29, 2014 10:49 AM
  • Wow you are genius :) . Its working fine. 
    Friday, August 29, 2014 10:52 AM
  • Sir i have also one question i am trying the code which i post put into the below code -

    Can you please help me one for the exact query for below code

    CREATE PROCEDURE [dbo].[GetImagesPageWise]
        @PageIndex INT = 1
       ,@PageSize INT = 3
       ,@PageCount INT OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON;
     
        SELECT ROW_NUMBER() OVER
                (
                      ORDER BY [Id] ASC
                )AS RowNumber
          ,Id
           ,Name
          ,Url
        INTO #Results
        FROM [Images]
     
          DECLARE @RecordCount INT
        SELECT @RecordCount = COUNT(*) FROM #Results
     
          SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
        PRINT       @PageCount
              
        SELECT * FROM #Results
        WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
        
        DROP TABLE #Results
    END
    

     


    Friday, August 29, 2014 11:12 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> I need to use ROW_NUMBER() in the following Query - <<

    No, you need to start over and learn how to do a data model. The name of a procedure is “<verb>_<object>” and never use a meta data prefix.

    Your use of “-tbl” is called tibbling and it is a version of the flaw. We really make of peoiple who do this. Read Phil Factor's article on it. 

    Why do you have dynamic SQL? This is how you tell the world that you have no idea what you are doing until a random stranger makes a decision for you. 

    SQL is a declarative language. A declarative language does not use local variables. This is covered in as freshman course on programming language. 

    There is no such crap as a generic “category” in RDBMS; to be is to be something in particular. Splitting a “<something>_category” into multiple tables is a design flaw called attribute splitting.  

    Do you really need FIVE HUNDRED CHARACTERS for a category encoding?? The Dewey Decimal Classification put all of human knowledge in three digits at the top level. 

    Do you really need FIVE HUNDRED CHARACTERS for a category encoding?? The Dewey Decimal Classification put all of human knowledge in three digits at the top level. How much time and care did you use to create the categories? Did you use DOT? Here is a skeleton for you: 

    CREATE TABLE Contributor_Categories
    (contributor_category CHAR(5) NOT NULL PRIMARY KEY
      CHECK (contributor_category LIKE '[0-9][0-9][0-9][0-9][0-9]'),
     contributor_category_description VARCHAR(30) NOT NULL,
      ..);

    INSERT INTO Contributor_Categories
    VALUES ('11000', 'Photographer'),
           ('11001', 'Photographer- staff'),
           ('11002', 'Photographer- free lance'),
        .. ('11999', 'Photographer- miscellaneous'),
    ..; 

    Do you now how to design a data encoding? 

    There is no “mapping” in RDBMS; that is an OO thing we do not use.  Why do you need a SELECT DISTINCT? They are very, very rare in a properly designed schema. The use of keys and normal forms prevents redundancy. And so are OUTER JOINs because DRI guarantees matches. 

    We do not use assembly language flags in SQL, so your is_active is a disaster. We have status codes; a status is a state of being  

    A data element has one and only one name in a schema, but in your mess the “city_id” is called a “location_id” -- can it also become a squid_id? Why do you have only one city, which is what that table name says. 

    The idea of a “Photographer_Contributors” is absurd. A Photographer is a type of Contributor, isn't it? Would you also have a “Male_Personnel” and “Female_Personnel” and not a “Personnel” table? See how silly this is? 

    You also might want to learn why we got rid of Pascal and Camel case in program text years ago. They do not port to many ISO standards 

    My guess is that if we had a valid schema, this would be as simple as  this and would run several orders of magnitude faster and be smaller. 

    CREATE PROCEDURE Select_Photographers
    @in_contributor_category CHAR(5) 
    AS
    SELECT Contributors.*  -- never do * in production code!
      FROM Contributors
     WHERE contributor_status = 'active'
       AND @in_contributor_category = contributor_category
       AND contributor_category LIKE '11___' ;

    The last predicate assures we only look at photographers. 

    Post  DDL and let's see if you can be fixed. 

       

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by rajeev0011 Saturday, August 30, 2014 5:18 AM
    • Unmarked as answer by rajeev0011 Saturday, August 30, 2014 5:18 AM
    Friday, August 29, 2014 5:41 PM