Query select.

Question

Post reply

Query select.

Dee Dee-422077

Ten Centuries

Points: 1082

April 22, 2021 at 9:25 pm

Go to Answer

#3874535

Hi,

I have a sample data and table as:

Field0Field1Field2Field3Field4Field5
1000Text1Text2Text3Text4Text5
1000Text1Text2Text3  
1001Text6Text7Text8Text9Text10
1002Text11Text12Text13Text14Text15
1003Text16Text17Text18Text19Text20
1003Text16Text17Text18
1005    Text16Text17Text18

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:

Field0Field1Field2Field3Field4Field5
1000Text1Text2Text3Text4Text5
1001Text6Text7Text8Text9Text10
1002Text11Text12Text13Text14Text15
1003Text16Text17Text18Text19Text20
1005    Text16Text17Text18

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

This topic was modified 3 years, 12 months ago by Dee Dee-422077.

Steve Collins

SSCrazy

Points: 2192

April 22, 2021 at 11:06 pm

Answer

#3874567

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

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

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

ScottPletcher SSC Guru Points: 101187 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 247137 More actions · Answer 2

Another alternative

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

Phil Parkin SSC Guru Points: 247137 More actions · Answer 3

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

Query select.

Cookies on SQLServerCentral