Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
need to get only distinct rows with using CTE

Answered need to get only distinct rows with using CTE

  • Thursday, March 15, 2012 12:30 PM
     
     
    Sign In to Vote
    0

    I have 2 Tables, Table A and Table B as like below:

    I need output as like below.

    Please let me know how using select query I can extract the data. There will be no temp table or table variable.

    Thanks,

    Saikat

     

All Replies

  • Thursday, March 15, 2012 12:34 PM
     
      Has Code
    Sign In to Vote
    0

    the below query might help

    select DISTINCT A.banumber,material,BusinessArrangementID
FROM tableA A
JOIN tableB B
ON   A.banumber = b.banumber

    Nothing is Permanent... even Knowledge....
    My Blog

     
  • Thursday, March 15, 2012 12:40 PM
     
     
    Sign In to Vote
    0

    Hi Surendra,

    INNER JOIN will not work here because BANumber is not unique.So if I use JOIN it will work like CROSS JOIN.

    Thanks,
    Saikat

     
  • Thursday, March 15, 2012 12:44 PM
    Moderator
     
     
    Sign In to Vote
    0

    Please, post table schema, sample data in the form of "insert" statements, and the logic to apply in order to get the expected result. This way we do not have to guess data types, column names, etc, and just copying and pasting will be enough to reproduce the problem in our side.

    AMB

    Some guidelines for posting questions...

     
  • Thursday, March 15, 2012 12:47 PM
     
      Has Code
    Sign In to Vote
    0

    you can use this if help :

    DECLARE @A TABLE(BANUMBER INT,businessassignmentID INT)
DECLARE @B TABLE(BANUMBER INT,Material VARCHAR(50))

INSERT INTO @A
SELECT 9077162,5020
UNION
SELECT 9077162,5021
UNION
SELECT 9077162,5022
UNION
SELECT 9077162,5023
UNION
SELECT 9077162,5024


INSERT INTO @B
SELECT 9077162,'0000000000000002000885'
UNION
SELECT 9077162,'GEN Chemical'
UNION
SELECT 9077162,'ST70.31'
UNION
SELECT 9077162,'3DT177.91'
UNION
SELECT 9077162,'3DT177.91'
UNION
SELECT 9077162,'3DT178.91'


;with cte as( select ROW_NUMBER() over (order by  BANUMBER asc) as row_ID,* from @A
),cte1 as( select ROW_NUMBER() over (order by  BANUMBER asc) as row_ID,* from @B)

select DISTINCT a.BANUMBER,b.Material,a.businessassignmentID FROM
cte a
INNER JOIN cte1 b on a.row_ID = b.row_ID

    Regards,

    Paresh

     
  • Thursday, March 15, 2012 12:56 PM
     
     Answered
    Sign In to Vote
    0

    There was duplicate issue in Table A. Now, it was fixed .Now in the Table A there is only distint rows storing based on BANumber.So I can now get the desired output by simple Inner join with Table A and Table B.

    Thanks Paresh for the attempt but now it was not needed.

    thanks

    Saikat