Rank over Partition by mit Where eingrenzen

[lurot]

Hallo Miteinander,

ich möchte diesen Ausdruck:

Rank() Over (Partition By BS_1.BS_id
Order By mandat.mandat_gültig_bis Desc) AS Rank

mit
Where Rank = 1
erweitern aber als Fehlermeldung erscheint ungültiger Spaltenname "Rank"

Ich möchte alle Zeilen die in der Spalte "Rank" eine 1 stehen haben.

Was bitte mache ich hier falsch?

Gruß
lurot

 

[castorp]

Leider hast Du uns nicht das komplette Statement gezeigt, also schmeiße ich mal meine Kristallkugel an:

Einen Spaltenalias kann man nicht auf der gleichen Ebene verwenden, in der er definiert wurde. Du musst die eigentlich Abfrage in eine sog. derived table ("sub-query") packen:

select *
from (
   select ...., rank() over (partition by ...) as rnk
  from the_table
) t
where rnk = 1;

 

[lurot]

Das ganze Statement sieht so aus:
Select
Distinct ZVK_1.ZVK_PK_Aussetzung_Mahnung_von
, ZVK_1.ZVK_PK_Aussetzung_Mahnung_bis
, ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung
, ZVK_1.ZVK_Saldo
, BS_1.BS_Nr
, BS_1.BS_Name
, BS_1.BS_Namenszusatz1
, BS_1.BS_Straße
, BS_1.BS_PLZ
, BS_1.BS_Postfach
, BS_1.BS_Ort
, BS_1.BS_id
, BS_1.BS_gesperrt
, ZVK_1.ZVK_gesperrt
, mandat.mandat_ist_aktiv
, mandat.mandat_gesperrt
, mandat.mandat_letztmalige_Nutzung_am
, mandat.mandat_gültig_von
, mandat.mandat_gültig_bis
, Rank() Over (Partition By BS_1.BS_id
Order By mandat.mandat_gültig_bis Desc) AS Rank
From dbo.ST_ZVK ZVK_1
Inner Join dbo.KT_ZVK KT_ZVK_1
On KT_ZVK_1.ZVK_id = ZVK_1.ZVK_id
Inner Join dbo.ST_BS BS_1
On BS_1.BS_id = KT_ZVK_1.bs_id
Left Join dbo.KT_mandat KT_mandat
On KT_mandat.bs_id = BS_1.BS_id
Left Join dbo.ST_mandat mandat
On KT_mandat.mandat_id = mandat.mandat_id
And mandat.mandat_gültig_bis >= '01.01.2020'
Where ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung = 10015

 

[akretschmer]

Ja, und das packst Du nun wie von @castorp gezeigt in ein Subselect.

 

[lurot]

Ich bin nur sehr laienhaft unterwegs, so etwa?

Select
Distinct ZVK_1.ZVK_PK_Aussetzung_Mahnung_von
, ZVK_1.ZVK_PK_Aussetzung_Mahnung_bis
, ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung
, ZVK_1.ZVK_Saldo
, BS_1.BS_Nr
, BS_1.BS_Name
, BS_1.BS_Namenszusatz1
, BS_1.BS_Straße
, BS_1.BS_PLZ
, BS_1.BS_Postfach
, BS_1.BS_Ort
, BS_1.BS_id
, BS_1.BS_gesperrt
, ZVK_1.ZVK_gesperrt
, mandat.mandat_ist_aktiv
, mandat.mandat_gesperrt
, mandat.mandat_letztmalige_Nutzung_am
, mandat.mandat_gültig_von
, mandat.mandat_gültig_bis
, Rank() Over (Partition By BS_1.BS_id
Order By mandat.mandat_gültig_bis Desc) AS Rank
From dbo.ST_ZVK ZVK_1
Inner Join dbo.KT_ZVK KT_ZVK_1
On KT_ZVK_1.ZVK_id = ZVK_1.ZVK_id
Inner Join dbo.ST_BS BS_1
On BS_1.BS_id = KT_ZVK_1.bs_id
Left Join dbo.KT_mandat KT_mandat
On KT_mandat.bs_id = BS_1.BS_id
Left Join dbo.ST_mandat mandat
On KT_mandat.mandat_id = mandat.mandat_id
And mandat.mandat_gültig_bis >= '01.01.2020'
Where ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung = 10015

select *
from(Select
Distinct ZVK_1.ZVK_PK_Aussetzung_Mahnung_von
, ZVK_1.ZVK_PK_Aussetzung_Mahnung_bis
, ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung
, ZVK_1.ZVK_Saldo
, BS_1.BS_Nr
, BS_1.BS_Name
, BS_1.BS_Namenszusatz1
, BS_1.BS_Straße
, BS_1.BS_PLZ
, BS_1.BS_Postfach
, BS_1.BS_Ort
, BS_1.BS_id
, BS_1.BS_gesperrt
, ZVK_1.ZVK_gesperrt
, mandat.mandat_ist_aktiv
, mandat.mandat_gesperrt
, mandat.mandat_letztmalige_Nutzung_am
, mandat.mandat_gültig_von
, mandat.mandat_gültig_bis
, Rank() Over (Partition By BS_1.BS_id
Order By mandat.mandat_gültig_bis Desc) AS Rank
From dbo.ST_ZVK ZVK_1
Inner Join dbo.KT_ZVK KT_ZVK_1
On KT_ZVK_1.ZVK_id = ZVK_1.ZVK_id
Inner Join dbo.ST_BS BS_1
On BS_1.BS_id = KT_ZVK_1.bs_id
Left Join dbo.KT_mandat KT_mandat
On KT_mandat.bs_id = BS_1.BS_id
Left Join dbo.ST_mandat mandat
On KT_mandat.mandat_id = mandat.mandat_id
And mandat.mandat_gültig_bis >= '01.01.2020'
Where ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung = 10015)
Where Rank =1

 

[Gimi27]

Hallo Lurot,

ich glaube dein Code ist schon richtig, aber du musst bein deinem "as Rank" das Rank in eckige Klammern setzten. So "as [Rank]"

Probiere es mal.

Gruß
Michael

 

[lurot]

Wir habe es so gelöst:

Select
Distinct ZVK_PK_Aussetzung_Mahnung_von
, ZVK_PK_Aussetzung_Mahnung_bis
, ZVK_PK_Aussetzungsgrund_Mahnung
, ZVK_Saldo
, BS_Nr
, BS_Name
, BS_Namenszusatz1
, BS_Straße
, BS_PLZ
, BS_Postfach
, BS_Ort
--, BS_id
--, BS_gesperrt
--, ZVK_gesperrt
, mandat_ist_aktiv
, mandat_gesperrt
, mandat_letztmalige_Nutzung_am
, mandat_gültig_von
, mandat_gültig_bis

from
(Select
Distinct ZVK_1.ZVK_PK_Aussetzung_Mahnung_von
, ZVK_1.ZVK_PK_Aussetzung_Mahnung_bis
, ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung
, ZVK_1.ZVK_Saldo
, BS_1.BS_Nr
, BS_1.BS_Name
, BS_1.BS_Namenszusatz1
, BS_1.BS_Straße
, BS_1.BS_PLZ
, BS_1.BS_Postfach
, BS_1.BS_Ort
, BS_1.BS_id
, BS_1.BS_gesperrt
, ZVK_1.ZVK_gesperrt
, mandat.mandat_ist_aktiv
, mandat.mandat_gesperrt
, mandat.mandat_letztmalige_Nutzung_am
, mandat.mandat_gültig_von
, mandat.mandat_gültig_bis
, Rank() Over (Partition By BS_1.BS_id
Order By mandat.mandat_gültig_bis Desc) AS Rank
From dbo.ST_ZVK ZVK_1
Inner Join dbo.KT_ZVK KT_ZVK_1
On KT_ZVK_1.ZVK_id = ZVK_1.ZVK_id
Inner Join dbo.ST_BS BS_1
On BS_1.BS_id = KT_ZVK_1.bs_id
Left Join dbo.KT_mandat KT_mandat
On KT_mandat.bs_id = BS_1.BS_id
Left Join dbo.ST_mandat mandat
On KT_mandat.mandat_id = mandat.mandat_id
And mandat.mandat_gültig_bis >= '01.01.2020'
Where ZVK_1.ZVK_PK_Aussetzungsgrund_Mahnung = 10015)as Rohdaten
Where Rank = 1