Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Omit row data for distinct columns

    Hi everybody,

    I have query result as
    Code:
    ID     COL_1   COL_2           COL_3    COL_4         SCORE    COL_6
    -----------------------------------------------------------------------------------------------
    22	120	    Adam 	        0            5.3	        20	    FALSE
    22	9	    Nina	        0	     0.65	        20	    TRUE
    23	66 	    Tom	        2457	     2.46 	        20	    TRUE
    23	54	    Anna 	        3338	     4.79	        20	    FALSE

    but I want to get result as below. I mean, one score number for each id.
    Code:
    ID     COL_1   COL_2           COL_3    COL_4         SCORE    COL_6
    -----------------------------------------------------------------------------------------------
    22	120	    Adam 	        0            5.3	        20	    FALSE
    22	9	    Nina	        0	     0.65	        0	    TRUE
    23	66 	    Tom	        2457	     2.46 	        20	    TRUE
    23	54	    Anna 	        3338	     4.79	        0	    FALSE

    How can I get this result?

    There should be many rows for each id. It is not fixed. And table is big.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2010
    Posts
    2
    Hello, I don't really understand what you require. The only difference that I can see in the result sets is the 2nd one has different values for col_6. Do you have a copy of the SQL and a bit more info as to what you are trying to do?

  3. #3
    Join Date
    Mar 2010
    Posts
    9
    ID SCORE
    -----------
    22 20
    22 20
    23 20
    23 20

    Data in table is as shown above. I want to get only one SCORE value for each ID as shown below.

    ID SCORE
    -----------
    22 20
    22 0
    23 20
    23 0

    Thank you in advance.

  4. #4
    Join Date
    Mar 2010
    Posts
    9
    I did it myself.

    Code:
    create table #test (ID int,SCORE int)
    
    insert into #test values(1,20);
    insert into #test values(1,20);
    insert into #test values(2,30);
    insert into #test values(2,30);
    insert into #test values(2,30);
    
    select * from #test
    
    select a.ID,
           case a.ord
             when 1 then
              a.SCORE
             ELSE
              0
           END AS SCORE
      from (select row_number() OVER(PARTITION BY ID ORDER BY ID) as ord,
                   id,
                   SCORE
              from #test) a
    Thank you all.
    Last edited by catastrophe; 04-14-10 at 03:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •