I would like that the rows are ranked on Aspez_ID attribute, and that only one row which has ASpez_ID = 1 is selected. The thing is - I want to select one and only one row from the dataset. Can you please help me? Here is the code:
LEFT OUTER JOIN (
SELECT dwh_prozess_id
, ONKZ
, TN_NUM
, Aspez_Id
, Tarif_Cd
, Lokation_Id
, VWArt_Id
, Geografische_RFN_JN
, Herkunft_Cd
-- , .... Felder, die noch benötigt werden
, RANK (Aspez_Id) AS Prio
FROM DB09_Prozess.TB0911_Basis_Konfiguration
WHERE arbeit_cd = 'Einrichten'
AND phase_cd = 'Durchgeführt'
) AS TB0911
ON t1.DWH_Prozess_Id = TB0911.DWH_Prozess_Id
AND t1.Herkunft_Cd = TB0911.Herkunft_Cd
As Bob Duell explained
ROW_NUMBER() OVER()
may be a better alternative asRANK() OVER()
allows for ties. (i.e. More than 1 record can have a given rank: 1,1,1,4,5,6,6,8)The QUALIFY clause works for window aggregate functions like HAVING does for normal aggregate functions. This would allow you to return records with a
Prio
of 1 or what ever value(s) you require.Hope this helps.