Query select.

  • Hi,

    I have a sample data and table as:

    Field0	Field1	Field2	Field3	Field4	Field5
    1000 Text1 Text2 Text3 Text4 Text5
    1000 Text1 Text2 Text3
    1001 Text6 Text7 Text8 Text9 Text10
    1002 Text11 Text12 Text13 Text14 Text15
    1003 Text16 Text17 Text18 Text19 Text20
    1003 Text16 Text17 Text18
    1005 Text16 Text17 Text18

    How do i query for if Field0 is the same, pick the one have data for Field4 and Field5, Ignore the one with "blank" in field4 and field5.

    The result:

    Field0	Field1	Field2	Field3	Field4	Field5
    1000 Text1 Text2 Text3 Text4 Text5
    1001 Text6 Text7 Text8 Text9 Text10
    1002 Text11 Text12 Text13 Text14 Text15
    1003 Text16 Text17 Text18 Text19 Text20
    1005 Text16 Text17 Text18

    Please advice and thanks in advance!

    Here is the create table and insert sample data

    CREATE TABLE T1 (
    Field0 int ,
    Field1 varchar(50) ,
    Field2 varchar(50) ,
    Field3 varchar(50) ,
    Field4 varchar(50),
    Field5 varchar(50)
    );
    INsert into T1 (Field0, Field1, Field2, Field3, Field4, Field5)
    Values
    ('1000','Text1','Text2','Text3','Text4','Text5'),
    ('1000','Text1','Text2','Text3',' ',' '),
    ('1001','Text6','Text7','Text8','Text9','Text10'),
    ('1002','Text11','Text12','Text13','Text14','Text15'),
    ('1003','Text16','Text17','Text18','Text19','Text20'),
    ('1003','Text16','Text17','Text18',' ',' ')
    ('1005','Text16','Text17','Text18',' ',' ')

    Ddee

  • If the display order of the result set doesn't matter, using TOP(1) WITH TIES and ordering by the ROW_NUMBER

    select top(1) with ties *
    from #t1
    order by row_number() over (partition by Field0
    order by Field4 desc,
    Field5 desc);

    If the display order matters, using a CTE and assigning the ROW_NUMBER to a column

    with t_cte as (
    select *, row_number() over (partition by Field0
    order by Field4 desc,
    Field5 desc) rn
    from #t1)
    select Field0, Field1, Field2, Field3, Field4, Field5
    from t_cte
    where rn=1
    order by Field0;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • An alternative:

    SELECT
    Field0,
    CASE WHEN Field1_with_Field4 <> '' THEN Field1_with_Field4 ELSE Field1_without_Field4 END AS Field1,
    CASE WHEN Field1_with_Field4 <> '' THEN Field2_with_Field4 ELSE Field2_without_Field4 END AS Field2,
    CASE WHEN Field1_with_Field4 <> '' THEN Field3_with_Field4 ELSE Field3_without_Field4 END AS Field3,
    CASE WHEN Field1_with_Field4 <> '' THEN Field4_with_Field4 ELSE Field4_without_Field4 END AS Field4,
    CASE WHEN Field1_with_Field4 <> '' THEN Field5_with_Field4 ELSE Field5_without_Field4 END AS Field5
    FROM (
    SELECT
    Field0,
    MAX(CASE WHEN Field4 <> '' THEN Field1 END) AS Field1_with_Field4,
    MAX(CASE WHEN Field4 <> '' THEN Field2 END) AS Field2_with_Field4,
    MAX(CASE WHEN Field4 <> '' THEN Field3 END) AS Field3_with_Field4,
    MAX(CASE WHEN Field4 <> '' THEN Field4 END) AS Field4_with_Field4,
    MAX(CASE WHEN Field4 <> '' THEN Field5 END) AS Field5_with_Field4,
    MAX(CASE WHEN Field4 = '' THEN Field1 END) AS Field1_without_Field4,
    MAX(CASE WHEN Field4 = '' THEN Field2 END) AS Field2_without_Field4,
    MAX(CASE WHEN Field4 = '' THEN Field3 END) AS Field3_without_Field4,
    MAX(CASE WHEN Field4 = '' THEN Field4 END) AS Field4_without_Field4,
    MAX(CASE WHEN Field4 = '' THEN Field5 END) AS Field5_without_Field4
    FROM dbo.T1
    GROUP BY Field0
    ) AS derived
    ORDER BY Field0

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Another alternative

    SELECT Field0
    ,Field1
    ,Field2
    ,Field3
    ,Field4 = MAX(Field4)
    ,Field5 = MAX(Field5)
    FROM dbo.T1
    GROUP BY Field0
    ,Field1
    ,Field2
    ,Field3;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • A more refined alternative, which does not run the risk of losing rows through aggregation:

    SELECT t.Field0
    ,t.Field1
    ,t.Field2
    ,t.Field3
    ,t.Field4
    ,t.Field5
    FROM dbo.T1 t
    WHERE (
    t.Field4 <> ''
    AND t.Field5 <> ''
    )
    OR
    (
    t.Field4 = ''
    AND t.Field5 = ''
    AND NOT EXISTS
    (
    SELECT t2.Field0
    ,t2.Field1
    ,t2.Field2
    ,t2.Field3
    FROM dbo.T1 t2
    WHERE NOT ( t2.Field4 = ''
    AND t2.Field5 = '')
    INTERSECT
    SELECT t.Field0
    ,t.Field1
    ,t.Field2
    ,t.Field3
    )
    );

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 5 posts - 1 through 5 (of 5 total)

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