Post reply

Display single row

  • August 12, 2014 at 8:14 am

    #289704

    Hi,

    I have requirement to display only a single unique row out of the 2 rows. In the attached screenshot for example NATBX7686 and NATBX7686A shows twice but container number CXDU 112544 6 is the same hence only one row should be outputted. The row without the ‘A’ suffix should be shown.

    Can somebody please advice how this can be accomplished ?

    Thanks.

  • August 12, 2014 at 8:19 am

    #1737230

    Use ROW_NUMBER() over that first column, and just select the one where the row number is 1.

    John

  • August 12, 2014 at 8:31 am

    #1737240

    Or maybe you could filter the columns with a condition. I'm not sure if it'll be better.

    SELECT column1,

    column2,

    column3,

    ...,

    columnN

    FROM mytable

    WHERE column1 NOT LIKE '%A'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 12, 2014 at 8:47 am

    #1737250

    pwalter83 (8/12/2014)

    Hi,

    I have requirement to display only a single unique row out of the 2 rows. In the attached screenshot for example NATBX7686 and NATBX7686A shows twice but container number CXDU 112544 6 is the same hence only one row should be outputted. The row without the ‘A’ suffix should be shown.

    Can somebody please advice how this can be accomplished ?

    Thanks.

    Thanks John,

    The problem is how do I differentiate between NATBX7686 and NATBX7686A before using ROW_. The other issue is there are many rows in the table as mentioned in the example.

    Thanks.

  • August 12, 2014 at 9:15 am

    #1737265

    Luis Cazares (8/12/2014)

    Or maybe you could filter the columns with a condition. I'm not sure if it'll be better.

    SELECT column1,

    column2,

    column3,

    ...,

    columnN

    FROM mytable

    WHERE column1 NOT LIKE '%A'

    Thanks Luis, Unfortunately all the values ending in 'A' are not duplicate. The 2 values should be similar with only difference being one of them ends in 'A' like in the examples below:

    NATBXC7722

    NATBXC7722A

    or

    CDRTU52471

    CDRTU52471A

    In this case, I need to extract only the first values not ending in A :

    NATBXC7722

    CDRTU52471

    Thanks.

  • August 12, 2014 at 9:22 am

    #1737269

    John Mitchell-245523 (8/12/2014)

    Use ROW_NUMBER() over that first column, and just select the one where the row number is 1.

    John

    Thanks John,

    The problem is how do I differentiate between NATBX7686 and NATBX7686A before using ROW_. The other issue is there are many rows in the table like I mentioned in the example.

    Thanks.

  • August 12, 2014 at 9:30 am

    #1737273

    pwalter83 (8/12/2014)

    John Mitchell-245523 (8/12/2014)

    Use ROW_NUMBER() over that first column, and just select the one where the row number is 1.

    John

    Thanks John,

    The problem is how do I differentiate between NATBX7686 and NATBX7686A before using ROW_. The other issue is there are many rows in the table like I mentioned in the example.

    Thanks.

    some sample table/data/query/results scripts provided by you will probably move this forward much quicker.

    we can see what you want without guessing.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • August 12, 2014 at 3:26 pm

    #1737378

    pwalter83 (8/12/2014)

    pwalter83 (8/12/2014)

    Hi,

    I have requirement to display only a single unique row out of the 2 rows. In the attached screenshot for example NATBX7686 and NATBX7686A shows twice but container number CXDU 112544 6 is the same hence only one row should be outputted. The row without the ‘A’ suffix should be shown.

    Can somebody please advice how this can be accomplished ?

    Thanks.

    Thanks John,

    The problem is how do I differentiate between NATBX7686 and NATBX7686A before using ROW_. The other issue is there are many rows in the table as mentioned in the example.

    Thanks.

    You need to add a where clause like

    WHERE ROW_NUMBER() OVER (

    PARTITION BY <columns containing the container number>

    ORDER BY <column which contains 'NATBX768', 'CDRTU52471', 'CDRTU52471A' etcetera>

    ) = 1

    (or add that condition to your existing where clause, if you already have a where clause in the query).

    Tom

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply