Help needed for an SQL

Transact-SQL
1

Hello,

I have below SQL

select
	TH.Barkod,
	H.HastaAdiSoyadi, 
	H.Uyrugu, 
	TH.Bolumu, 
	TGD.GrupKimlik,
	TG.GrupAdi
FROM 
  TTetkiklerHareket AS TH  
  left join THastaListesi AS H ON TH.TC = H.HastaTC 
  left join TTestGruplariDetay AS TGD ON TGD.TestKimlik = TH.TetkikTestKodu 
  left join TTestGruplari AS TG ON TGD.GrupKimlik = TG.Kimlik 
group by
  TH.Barkod,
  H.HastaAdiSoyadi,
  H.Uyrugu, 
  TH.Bolumu, 
  TGD.GrupKimlik,
  TG.GrupAdi

which gives below output

Barkod HastaAdiSoyadi Uyrugu Bolumu GrupKimlik GrupAdi
20212000000029 ABDULLAH TÜRE T.C. 7 ORNEK GRUP
20212000000030 ABDULLAH TÜRE T.C. 7 ORNEK GRUP
20212000000031 ABDULLAH TÜRE T.C. 7 ORNEK GRUP
20212000000032 ABDULLAH TÜRE T.C. 1. KAT LABORATUVAR NULL NULL
20212000000033 ABDULLAH TÜRE T.C. NULL NULL
20212000000034 ABDULLAH TÜRE T.C. NULL NULL
20212000000035 ABDULLAH TÜRE T.C. NULL NULL
20212000000036 ADNAN AKBALI T.C. 6 GENEL GRUP
20212000000037 ADNAN AKBALI T.C. 6 GENEL GRUP

What I am trying to achieve is to have GroupKimlik NULL as is, but first records from each individual GroupKimlik. In other words, below table

Barkod HastaAdiSoyadi Uyrugu Bolumu GrupKimlik GrupAdi
20212000000029 ABDULLAH TÜRE T.C. 7 ORNEK GRUP
20212000000032 ABDULLAH TÜRE T.C. 1. KAT LABORATUVAR NULL NULL
20212000000033 ABDULLAH TÜRE T.C. NULL NULL
20212000000034 ABDULLAH TÜRE T.C. NULL NULL
20212000000035 ABDULLAH TÜRE T.C. NULL NULL
20212000000036 ADNAN AKBALI T.C. 6 GENEL GRUP

I have tried to use union and separate NULL vs NOT NULL values, but I could not list only the first record from each group.

Any help is appreciated.

Thanks & Regards,
Ertan

2

hi

hope this helps

please click here for the Sample Data Create Script 
create table test_data 
(
Barkod	 bigint , HastaAdiSoyadi	varchar(100) , Uyrugu	varchar(10) , Bolumu varchar(50),	GrupKimlik int null , 	GrupAdi varchar(100)
)

insert into test_data select 20212000000029,'ABDULLAH TÜRE','T.C.','					',7		,'ORNEK GRUP  '
insert into test_data select 20212000000030,'ABDULLAH TÜRE','T.C.','					',7		,'ORNEK GRUP  '
insert into test_data select 20212000000031,'ABDULLAH TÜRE','T.C.','					',7		,'ORNEK GRUP  '
insert into test_data select 20212000000032,'ABDULLAH TÜRE','T.C.','1. KAT LABORATUVAR	',NULL	,NULL		  
insert into test_data select 20212000000033,'ABDULLAH TÜRE','T.C.','					',NULL	,NULL		  
insert into test_data select 20212000000034,'ABDULLAH TÜRE','T.C.','					',NULL	,NULL		  
insert into test_data select 20212000000035,'ABDULLAH TÜRE','T.C.','					',NULL	,NULL		  
insert into test_data select 20212000000036,'ADNAN AKBALI' ,'T.C.','					',6		,'GENEL GRUP  '
insert into test_data select 20212000000037,'ADNAN AKBALI' ,'T.C.','					',6		,'GENEL GRUP  '

SELECT NULL AS rn,
       *
FROM   test_data
WHERE  grupkimlik IS NULL
UNION ALL
SELECT *
FROM   (SELECT Row_number()
                 OVER(
                   partition BY grupkimlik
                   ORDER BY barkod ASC ) AS rn,
               *
        FROM   test_data
        WHERE  grupkimlik IS NOT NULL) cte
WHERE  rn = 1

image
image875×212 7.05 KB

1 Like
3 
;WITH cte1 AS (
    SELECT
	    TH.Barkod,
	    H.HastaAdiSoyadi, 
	    H.Uyrugu, 
	    TH.Bolumu, 
	    TGD.GrupKimlik,
	    TG.GrupAdi,
        ROW_NUMBER() OVER(PARTITION BY HastaAdiSoyadi, Uyrugu, Bolumu, GrupAdi ORDER BY GrupKimlik, Barkod) AS row_num
    FROM 
        TTetkiklerHareket AS TH  
        left join THastaListesi AS H ON TH.TC = H.HastaTC 
        left join TTestGruplariDetay AS TGD ON TGD.TestKimlik = TH.TetkikTestKodu 
        left join TTestGruplari AS TG ON TGD.GrupKimlik = TG.Kimlik 
    group by
        TH.Barkod,
        H.HastaAdiSoyadi,
        H.Uyrugu, 
        TH.Bolumu, 
        TGD.GrupKimlik,
        TG.GrupAdi
)
SELECT Barkod, HastaAdiSoyadi, Uyrugu, Bolumu, GrupKimlik, GrupAdi
FROM cte1
WHERE GrupKimlik IS NULL OR row_num = 1

Or, using the sample data from harishgg1:

;WITH cte1 AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY HastaAdiSoyadi, Uyrugu, Bolumu, GrupAdi ORDER BY GrupKimlik, Barkod) AS row_num
    FROM 
        test_data
)
SELECT Barkod, HastaAdiSoyadi, Uyrugu, Bolumu, GrupKimlik, GrupAdi
FROM cte1
WHERE GrupKimlik IS NULL OR row_num = 1
ORDER BY Barkod
1 Like
4

I could get correct result using something as following. It is slightly long, but works for me.

with abc as (
select
	TH.Barkod,
	H.HastaAdiSoyadi, 
	H.Uyrugu, 
	TH.Bolumu, 
	TGD.GrupKimlik,
	TG.GrupAdi
FROM 
  TTetkiklerHareket AS TH  
  left join THastaListesi AS H ON TH.TC = H.HastaTC 
  left join TTestGruplariDetay AS TGD ON TGD.TestKimlik = TH.TetkikTestKodu 
  left join TTestGruplari AS TG ON TGD.GrupKimlik = TG.Kimlik 
where 
  GrupKimlik is not null
group by
  TH.Barkod,
  H.HastaAdiSoyadi,
  H.Uyrugu, 
  TH.Bolumu, 
  TGD.GrupKimlik,
  TG.GrupAdi
)


select
	NULL as rn,
  TH.Barkod,
	H.HastaAdiSoyadi, 
	H.Uyrugu, 
	TH.Bolumu, 
	TGD.GrupKimlik,
	TG.GrupAdi
FROM 
  TTetkiklerHareket AS TH  
  left join THastaListesi AS H ON TH.TC = H.HastaTC 
  left join TTestGruplariDetay AS TGD ON TGD.TestKimlik = TH.TetkikTestKodu 
  left join TTestGruplari AS TG ON TGD.GrupKimlik = TG.Kimlik 
where 
  GrupKimlik is null
group by
  TH.Barkod,
  H.HastaAdiSoyadi,
  H.Uyrugu, 
  TH.Bolumu, 
  TGD.GrupKimlik,
  TG.GrupAdi


UNION ALL

SELECT 
  *
FROM   
  (SELECT Row_number()
                 OVER(
                   partition BY grupkimlik
                   ORDER BY barkod ASC ) AS rn,
               *
        FROM   abc
        WHERE  grupkimlik IS NOT NULL) cte
WHERE  rn = 1
5

Hello ScottPletcher,

I solved my problem in a longer way before your reply. So I marked harishgg1 reply as solution.

Though I really liked simplicity of your solution.

Thanks.

6

The top lines of each thread is.

If someone answers your question or provides a helpful response, please take a second to LIKE that reply. It's a nice way to say Thank You to someone that helped you out.

You do that by clicking on the little heart at the bottom of a post (either topic or reply).