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
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
April 22, 2021 at 11:14 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 23, 2021 at 6:36 am
Another alternative
SELECT Field0
,Field1
,Field2
,Field3
,Field4 = MAX(Field4)
,Field5 = MAX(Field5)
FROM dbo.T1
GROUP BY Field0
,Field1
,Field2
,Field3;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 23, 2021 at 7:19 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply