none
Hiding some repeated data RRS feed

  • Question

  • Hi all

    We have a table which have few data like this:

    OrderNo   Invoice

    418024    6204111719
    418025    6207118005
    418132    6207118006
    418133    6207118128
    418184    6207118128
    418026    6207118130
    418108    6207118130
    418134    6207118130
    418185    6207118130

    As you can see the 4th and 5th rows have the same invoice number, and the 6th to 9th rows also have the same number, what I want is to select the data that hide the repeated invoice number, but the order number should display because it's distinct, and the invoice number is already sorted.

    For example, the select result for the table above should be like this:

    OrderNo   Invoice

    418024    6204111719
    418025    6207118005
    418132    6207118006
    418133    6207118128
    418184 
    418026    6207118130
    418108 
    418134 
    418185 

    any one give some idea, please.

    Monday, July 9, 2018 1:50 PM

Answers

  • Good day,

    I agree with Tom basically but not 100% ;-)

    To convert the data from integers to string and have blank string for duplicate data is something that probably should be done in the client side and not in the server side. This is not related to getting the data but to displaying the data, but...

    Having null instead of duplicate data might have a huge advantage when it is done in the server side (depending on the provider we use to connect the server), since the size of the data that needed to pass from the server to the client can be reduced dramatically (which is basically my golden rule especially with remote server, about what should be done in the server side and what should be done in the client side).

    Therefore, here are the queries for both options, for your choose:

    drop table if exists T;
    create table T(OrderNo int, Invoice bigint)
    GO
    
    Insert T (OrderNo,Invoice) values
    (418024, 6204111719),
    (418025, 6207118005),
    (418132, 6207118006),
    (418133, 6207118128),
    (418184, 6207118128),
    (418026, 6207118130),
    (418108, 6207118130),
    (418134, 6207118130),
    (418185, 6207118130)
    GO
    
    select * from T
    GO
    
    -- solution that make sense (leave data as BIGINT and use null for duplicate)
    ;with MyCTE as (
    	select OrderNo, Invoice, 
    		RN = ROW_NUMBER() OVER (partition by Invoice order by OrderNo)
    	from T
    ) 
    select OrderNo, Invoice = CASE WHEN RN = 1 then Invoice ELSE null END
    from MyCTE
    GO
    
    
    -- solution that Does NOT make sense in the server side, since this is displaying!
    ;with MyCTE as (
    	select OrderNo, Invoice, 
    		RN = ROW_NUMBER() OVER (partition by Invoice order by OrderNo)
    	from T
    ) 
    select OrderNo, Invoice = CASE WHEN RN = 1 then CONVERT(NVARCHAR(10), Invoice) ELSE '' END
    from MyCTE
    GO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    Monday, July 9, 2018 2:06 PM
    Moderator
  • CREATE TABLE mytable(
       OrderNo INTEGER  NOT NULL  
      ,Invoice varchar(20)  NOT NULL
    );
    INSERT INTO mytable(OrderNo,Invoice) VALUES
     (418024,6204111719)
    ,(418025,6207118005)
    ,(418132,6207118006)
    ,(418133,6207118128)
    ,(418184,6207118128)
    ,(418026,6207118130)
    ,(418108,6207118130)
    ,(418134,6207118130)
    ,(418185,6207118130);
    
     Select OrderNo,
     
     Case when  row_number() Over(Partition by Invoice Order by OrderNo) >1 then ''
     else invoice end Invoice from mytable
    
    drop table mytable

    • Proposed as answer by ren.ren Monday, July 9, 2018 2:17 PM
    • Marked as answer by William A Wang Tuesday, July 10, 2018 9:55 AM
    Monday, July 9, 2018 2:03 PM
    Moderator

All replies

  • That is not something you do at the query level, you would do that at your presentation layer, whatever that is.

    Monday, July 9, 2018 1:52 PM
    Moderator
  • CREATE TABLE mytable(
       OrderNo INTEGER  NOT NULL  
      ,Invoice varchar(20)  NOT NULL
    );
    INSERT INTO mytable(OrderNo,Invoice) VALUES
     (418024,6204111719)
    ,(418025,6207118005)
    ,(418132,6207118006)
    ,(418133,6207118128)
    ,(418184,6207118128)
    ,(418026,6207118130)
    ,(418108,6207118130)
    ,(418134,6207118130)
    ,(418185,6207118130);
    
     Select OrderNo,
     
     Case when  row_number() Over(Partition by Invoice Order by OrderNo) >1 then ''
     else invoice end Invoice from mytable
    
    drop table mytable

    • Proposed as answer by ren.ren Monday, July 9, 2018 2:17 PM
    • Marked as answer by William A Wang Tuesday, July 10, 2018 9:55 AM
    Monday, July 9, 2018 2:03 PM
    Moderator
  • Good day,

    I agree with Tom basically but not 100% ;-)

    To convert the data from integers to string and have blank string for duplicate data is something that probably should be done in the client side and not in the server side. This is not related to getting the data but to displaying the data, but...

    Having null instead of duplicate data might have a huge advantage when it is done in the server side (depending on the provider we use to connect the server), since the size of the data that needed to pass from the server to the client can be reduced dramatically (which is basically my golden rule especially with remote server, about what should be done in the server side and what should be done in the client side).

    Therefore, here are the queries for both options, for your choose:

    drop table if exists T;
    create table T(OrderNo int, Invoice bigint)
    GO
    
    Insert T (OrderNo,Invoice) values
    (418024, 6204111719),
    (418025, 6207118005),
    (418132, 6207118006),
    (418133, 6207118128),
    (418184, 6207118128),
    (418026, 6207118130),
    (418108, 6207118130),
    (418134, 6207118130),
    (418185, 6207118130)
    GO
    
    select * from T
    GO
    
    -- solution that make sense (leave data as BIGINT and use null for duplicate)
    ;with MyCTE as (
    	select OrderNo, Invoice, 
    		RN = ROW_NUMBER() OVER (partition by Invoice order by OrderNo)
    	from T
    ) 
    select OrderNo, Invoice = CASE WHEN RN = 1 then Invoice ELSE null END
    from MyCTE
    GO
    
    
    -- solution that Does NOT make sense in the server side, since this is displaying!
    ;with MyCTE as (
    	select OrderNo, Invoice, 
    		RN = ROW_NUMBER() OVER (partition by Invoice order by OrderNo)
    	from T
    ) 
    select OrderNo, Invoice = CASE WHEN RN = 1 then CONVERT(NVARCHAR(10), Invoice) ELSE '' END
    from MyCTE
    GO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]




    Monday, July 9, 2018 2:06 PM
    Moderator
  • This is more of a presentation requirement and can be very easily implemented in your front end application. For example if its for a SSRS report then you can use Hide Duplicates property available from textbox properties tab and select scope at which you can apply this (detail level/group level etc). Based on the setting it will exclude displaying the duplicate values within the prescribed scope.

    https://glutenfreesql.wordpress.com/2012/10/02/ssrs-hide-duplicates/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 9, 2018 2:34 PM
  • thanks both of you
    Tuesday, July 10, 2018 5:28 AM
  • no, actually this is not for SSRS, we collect these data to generate and print receipt report, we also could remove the extra data in logical level but it takes extra works, remove in DB level should be much more easier.
    Tuesday, July 10, 2018 5:32 AM
  • no, actually this is not for SSRS, we collect these data to generate and print receipt report, we also could remove the extra data in logical level but it takes extra works, remove in DB level should be much more easier.

    In your case it might be but if you see the logic you've to apply for that, its actually making it clumsy. The reason why I say this is because for achieving this presentation requirement you would have to convert the base datatype of the column to character based if its numeric (as '' is not numeric by itself). So where this can cause an issue is when you try to do some manipulations like sorting,comparison etc with this converted data in your front end(i.e where you use this generated data). That's why its always recommended to do this in presentation layer and we advocate against doing it in SQL

    Anyways in your case since Invoice is of character based type, it doesnt make much difference

    You can use a logic like below

    DECLARE @t table(
       OrderNo INTEGER  NOT NULL  
      ,Invoice varchar(20)  NOT NULL
    );
    INSERT INTO @t(OrderNo,Invoice) VALUES
     (418024,6204111719)
    ,(418025,6207118005)
    ,(418132,6207118006)
    ,(418133,6207118128)
    ,(418184,6207118128)
    ,(418026,6207118130)
    ,(418108,6207118130)
    ,(418134,6207118130)
    ,(418185,6207118130);
    
    
    SELECT OrderNo,CASE WHEN OrderNo = MIN(OrderNo) OVER (PARTITION BY Invoice) THEN Invoice ELSE '' END AS InvoiceDisplay
    FROM @t
    ORDER BY Invoice,OrderNo
    
    
    /*
    Output
    ------------------------------
    OrderNo	InvoiceDisplay
    ------------------------------
    418024	6204111719
    418025	6207118005
    418132	6207118006
    418133	6207118128
    418184	
    418026	6207118130
    418108	
    418134	
    418185	
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, July 10, 2018 5:40 AM
  • no, actually this is not for SSRS, we collect these data to generate and print receipt report, we also could remove the extra data in logical level but it takes extra works, remove in DB level should be much more easier.

    Hi William,

    Thanks for your reply.

    Per your detailed requirement, the solutions provided by JingYang and Ronen should be suitable for you. You could take it into your application.

    If this solves your problem, don't forget to close the thread by marking useful replies as answer. :-)

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 6:24 AM
    Moderator
  • Check this too..

    select OrderNo, case when lag(Invoice) over(order by Invoice) is null or Invoice <> lag(Invoice) over(order by Invoice) then Invoice else '' end as Invoice from TableName
    Tuesday, July 10, 2018 9:53 AM