none
t-sql compare values in same column of a table RRS feed

  • Question

  • Hi, 

    table 1

    id reason appid index

    1  abc       123   0
    2  abc       123   1

    3  xyz        456  0

    4  pqr        789  0
    5  pqr        789  1

    6  fgh        333 0
    7  bvc        333 1

    table 2

    appid  date
    123     1/1/2001
    789      6/6/2001

    I need to get all rows where appid AND reason is same but will have different index values. so, output must give me rows IDs - 1,2,4,5 and date column from table 2
    6 & 7 will not come as the reason column has different values for the same appid.

    any help is appreciated!!

    • Moved by Olaf HelperMVP Monday, June 10, 2013 8:43 AM Moved from "SQL Database Engine" to a more related forum for a better response.
    Monday, June 10, 2013 8:33 AM

Answers

  • Try

    with cte as
    (select appid,reason,count(distinct index) cnt
    from tab1
    group by appid,reason
    having count(distinct index)>1)
    select *
    from tab1 a
    where exists(select *
    from cte b
    where b.appid=a.appid
    and b.reason=a.reason);
    


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by MandarAlawani Monday, June 10, 2013 9:41 AM
    Monday, June 10, 2013 8:56 AM

All replies

  • Try the below:

    Create Table Table1(id int, reason varchar(10),appid int, sindex bit) Insert into Table1 Values (1,'abc',123,0), (2, 'abc',123,1), (3, 'xyz',456,0), (4, 'pqr',789,0), (5, 'pqr',789,1), (6, 'fgh',333,0), (7,'bvc',333,1) Create Table Table2(appid int, sDate Date) Insert into Table2 Values (123,'1/1/2001'), (789,'6/6/2001') ;With cte As( Select A.AppId,B.sDate,ROW_NUMBER()Over(Partition by A.AppId,Reason ORder by Id asc) Rn ,COUNT(A.sIndex)Over(Partition by A.AppId,Reason,SIndex) Cnt From Table1 A Inner Join Table2 B On A.AppID = B.AppId ) ,cte1 As ( Select cte.AppId,sDate,COUNT(AppID) Over(PARTITION by AppID) Cnt from cte Where rn>1 and Cnt=1 ) Select A.*,B.sDate From Table1 A Inner Join cte1 B On A.AppId = B.AppID Drop table Table1,Table2



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, June 10, 2013 8:55 AM
  • Try

    with cte as
    (select appid,reason,count(distinct index) cnt
    from tab1
    group by appid,reason
    having count(distinct index)>1)
    select *
    from tab1 a
    where exists(select *
    from cte b
    where b.appid=a.appid
    and b.reason=a.reason);
    


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by MandarAlawani Monday, June 10, 2013 9:41 AM
    Monday, June 10, 2013 8:56 AM
  • Try this,

    create table t1 (id int,reason varchar(100), appid int,index_id tinyint)
    create table t2 (appid int,date_column date)
    
    
    insert into t1 values
    (1,'abc',123,0),(2,'abc',123,1),(3,'xyz', 456,0),(4,'pqr', 789,0),(5,'pqr', 789,1),(6,'fgh', 333,0),(7,'bvc',333, 1)
    
    insert into t2 values (123,'1/1/2001'),(789,'6/6/2001')
    
    ;with cte(appid,reason)
    as
    (
    select appid,reason from t1 where index_id=0
    UNION ALL 
    select appid,reason from t1 where index_id=1
    )
    select T1.id, T1.appid,T1.reason,t2.date_column from t1
    inner join (
    select appid,reason from cte
    group by appid,reason having COUNT(1)>1) A 
    ON T1.appid=A.appid
    left join t2 
    ON T1.appid=T2.appid

    I used left join for table 2, as not sure whether all the appid will have an entry in that. If entries are available for all the appids , change it to inner join.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, June 10, 2013 8:57 AM
  • try,

    declare @table1 table(id int,reason nvarchar(10), appid int, [index] int)
    insert into @table1 values(1,'abc' ,123,0)
    insert into @table1 values(2,  'abc',123,1)
    insert into @table1 values(3,  'xyz',456,0)
    insert into @table1 values(4,  'pqr',789,0)
    insert into @table1 values(5,  'pqr',789,1)
    insert into @table1 values(6,  'fgh',333,0)
    insert into @table1 values(7 , 'bvc',333,1)
    declare @table2 table(appid int,[date] datetime)
    insert into @table2 values(123 ,'1/1/2001')
    insert into @table2 values(789 ,'6/6/2001')
    -----------------------------------------------------------
    select id,reason,appid,[index],[date] from (
    	select a.id,a.reason,a.appid,a.[index],b.[date],
    	row_number() over (partition by a.reason,a.appid order by a.reason,a.appid) as RowID 
    	from @table1 a inner join @table2 b on a.appid=b.appid
    ) x
    where x.rowid in (1,2)


    Regards, RSingh

    Monday, June 10, 2013 9:03 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 (you failed). Temporal data should use ISO-8601 formats (you failed again). Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Please tell us why you are special and do not have to be polite. Here is my guess; please note the UNIQUE constraint. 80-95% of the work in SQL is done in the DDL, not the DML. Failure to post DDL tells the world that you do not know how to program in SQL.

    CREATE TABLE Alpha

    (alpha_id INTEGER NOT NULL PRIMARY KEY,

    reason_code CHAR(3) NOT NULL,

    app_id INTEGER NOT NULL,

    index_flg SMALLINT DEFAULT 0 NOT NULL

    CHECK (index_flg IN (0,1)),

    UNIQUE (reason_code, app_id, index_flg));

    INSERT INTO Alpha

    VALUES

    (1, 'abc', 123, 0),
    (2, 'abc', 123, 1),

    (3, 'xyz', 456, 0),

    (4, 'pqr', 789, 0),
    (5, 'pqr', 789, 1),
    (6, 'fgh', 333, 0),
    (7, 'bvc', 333, 1);

    >> I need to get all rows where app_id and reason_code are the same, but will have different index_flg values. <<

    SELECT MIN(alpha_id), MAX(alpha_id), reason_code, app_id

    FROM Alpha

    GROUP BY reason_code, app_id

    HAVING MIN(index_flg) = 0

    AND MAX(index_flg) = 1;

    ==================

    1 2 abc 123

    4 5 pqr 789


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

    Monday, June 10, 2013 1:09 PM