Ranking rows and selecting one

Question

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

Do you want only one row returned for the entire query or one unique row per key (possibly dwh_prozess_id)? In either case, you probably should use `ROW_NUMBER` not `RANK`. — BellevueBob, Sep 13 '12 at 13:30

score 2 · Answer 1 · answered Sep 13 '12 at 18:13

As Bob Duell explained ROW_NUMBER() OVER() may be a better alternative as RANK() 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.

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
          , ROW_NUMBER() OVER(PARTITION BY {partition group} /* PARTITION BY is optional */
                              ORDER BY {ordering group}) AS Prio

   FROM   DB09_Prozess.TB0911_Basis_Konfiguration 
   WHERE  arbeit_cd = 'Einrichten'
       AND phase_cd = 'Durchgeführt'
   QUALIFY Prio = 1

Hope this helps.

Ranking rows and selecting one

1 Answers1