Consulta con Group By y Max
 

Hola a todos :

Utilizo Firebird 2.1 y quiero realizar una consulta SQL.

Tengo un Dataset con 5 campos (C1, C2, C3, C4 y C5) y dos registros con sus respectivos valores :

Quiere generar una sentencia SQL que me devuelta el registro completo, agrupado por C1, C2, C3 y teniendo en cuenta el máximo valor de C4 (C4 sólo puede ser 0 o 1).

Es decir, la consulta me tiene que devolver A B C 1 Y ya que agrupados por A, B y C, el máximo valor de C4 es 1, y (sobre todo !) necesito el valor Y del campo C5.

He intentado utilizar subconsultas dentro del propio Select y dentro de las condiciones When, pero no obtengo el resultado que deseo.

He simplificado al máximo el ejemplo para ponerlo aquí en el foro. Por supuesto que C1, C2 y C3 pueden adquirir cualquier otro valor, y en el Dataset puede haber más de 2 registros.

Gracias como siempre por vuestra ayuda.

Hola gluglu, puedes probar esta consulta:

Código SQL [-]
Select T.C1, T.C2, T.C3, T.C4, T.C5
From tabla T Inner Join (Select Max(C4) as C4
                              From Tabla Group By C1,C2,C3) as T1
On (T.C4=T1.C4)

Saluditos

Con la cláusula having que está explicada en éste documento
Saludos.

Gracias a todos por vuestros comentarios, pero sobre todo a Caro.

Justo ahora había encontrado ahora esa misma solución en otro sitio en Internet.

Lo he probabo ya y funciona ! ;)


Para el que quiere ver mi consulta completa, tal y como quedó :

Código SQL [-]
Select Q_SELA.OUT_DATE, Q_SELA.INTERNALNO, Q_SELA.WEEKEND,
       Q_SELA.CATEGORYNO, Q_SELA.SUBCATEGORYNO, Q_SELA.VIRTUALNO,
       Q_SELA.APPLYON, Q_SELA.REAL_ALLOTMENT_CLOSE, Q_SELA.QUANTITY
from
  (Select OUT_DATE, INTERNALNO, WEEKEND,
          CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
          1 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
   from
     (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select AID.OUT_DATE,
                AOL.INTERNALNO, AOL.WEEKEND,
                AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
         from AVAIL_ONLINE AOL
         left join AUTO_INC_DATE('03-24-2012', 3) AID
           on 1 = 1
         left join SEASONS_DATES SD
           on SD.CODENAME = AOL.SEASONCODE
           and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
         left join AVAIL_ONLINE_CONDITIONS AOC
           on AOC.INTERNALNO = AOL.INTERNALNO
         left join AVAIL_CALC_TEMP ACT
           on ACT.RANDOMNO    = 1
           and ACT.CATEGORYNO    = AOC.CATEGORYNO
           and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
           and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
           and ACT.DATE_AVAIL    = AID.OUT_DATE
         left join FIXEDVALUES FX
           on FX.BUILDINGNO = 2
           and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
         where AOL.BUILDINGNO  = 2
         and AOL.CATEGORYNO    = 1
         and ((AOL.DATEFROM   <= '03-26-2012'
               and AOL.DATETO >= '03-24-2012')
              or (AOL.SEASONCODE in
                  (Select Distinct(CODENAME) from SEASONS_DATES SD
                   where SD.DATEFROM <= '03-26-2012'
                   and SD.DATETO     >= '03-24-2012'
                  and SD.BUILDINGNO  = 2)))
         and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
              (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
         and ((Q_ANTIRELEASE is Null) or
              (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
              (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
         and ((Q_RELEASE is Null) or
              (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
              (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
         and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
              (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
         and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
      group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
               VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
      where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                        where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
      and Position('1' in Q_SEL.APPLYON) <> 0
   union
   Select OUT_DATE, INTERNALNO, WEEKEND,
          CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
          2 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
   from
     (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select AID.OUT_DATE,
                AOL.INTERNALNO, AOL.WEEKEND,
                AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
         from AVAIL_ONLINE AOL
         left join AUTO_INC_DATE('03-24-2012', 3) AID
           on 1 = 1
         left join SEASONS_DATES SD
           on SD.CODENAME = AOL.SEASONCODE
           and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
         left join AVAIL_ONLINE_CONDITIONS AOC
           on AOC.INTERNALNO = AOL.INTERNALNO
         left join AVAIL_CALC_TEMP ACT
           on ACT.RANDOMNO    = 1
           and ACT.CATEGORYNO    = AOC.CATEGORYNO
           and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
           and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
           and ACT.DATE_AVAIL    = AID.OUT_DATE
         left join FIXEDVALUES FX
           on FX.BUILDINGNO = 2
           and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
         where AOL.BUILDINGNO  = 2
         and AOL.CATEGORYNO    = 1
         and ((AOL.DATEFROM   <= '03-26-2012'
               and AOL.DATETO >= '03-24-2012')
              or (AOL.SEASONCODE in
                  (Select Distinct(CODENAME) from SEASONS_DATES SD
                   where SD.DATEFROM <= '03-26-2012'
                   and SD.DATETO     >= '03-24-2012'
                   and SD.BUILDINGNO  = 2)))
         and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
              (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
         and ((Q_ANTIRELEASE is Null) or
              (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
              (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
         and ((Q_RELEASE is Null) or
              (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
              (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
         and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
              (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
         and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
      group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
               VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
      where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                        where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
      and Position('2' in Q_SEL.APPLYON) <> 0) Q_SELA
left join
  (Select OUT_DATE, CATEGORYNO, SUBCATEGORYNO, VIRTUALNO, APPLYON,
          Max(WEEKEND) as WEEKEND
   from
     (Select OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             1 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
                CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
                APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
         from
           (Select AID.OUT_DATE,
                   AOL.INTERNALNO, AOL.WEEKEND,
                   AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                   AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
            from AVAIL_ONLINE AOL
            left join AUTO_INC_DATE('03-24-2012', 3) AID
              on 1 = 1
            left join SEASONS_DATES SD
              on SD.CODENAME = AOL.SEASONCODE
              and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
            left join AVAIL_ONLINE_CONDITIONS AOC
              on AOC.INTERNALNO = AOL.INTERNALNO
            left join AVAIL_CALC_TEMP ACT
              on ACT.RANDOMNO    = 1
              and ACT.CATEGORYNO    = AOC.CATEGORYNO
              and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
              and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
              and ACT.DATE_AVAIL    = AID.OUT_DATE
            left join FIXEDVALUES FX
              on FX.BUILDINGNO = 2
              and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
            where AOL.BUILDINGNO  = 2
            and AOL.CATEGORYNO    = 1
            and ((AOL.DATEFROM   <= '03-26-2012'
                  and AOL.DATETO >= '03-24-2012')
                 or (AOL.SEASONCODE in
                     (Select Distinct(CODENAME) from SEASONS_DATES SD
                      where SD.DATEFROM <= '03-26-2012'
                      and SD.DATETO     >= '03-24-2012'
                      and SD.BUILDINGNO  = 2)))
            and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
                 (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
            and ((Q_ANTIRELEASE is Null) or
                 (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
                 (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
            and ((Q_RELEASE is Null) or
                 (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
                 (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
            and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
                 (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
            and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
         group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
                  VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
         where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                           where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
         and Position('1' in Q_SEL.APPLYON) <> 0
      union
      Select OUT_DATE, INTERNALNO, WEEKEND,
             CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
             2 as APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
      from
        (Select Count(*) as NUM_HITS, OUT_DATE, INTERNALNO, WEEKEND,
                CATEGORYNO, SUBCATEGORYNO, VIRTUALNO,
                APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY
         from
           (Select AID.OUT_DATE,
                   AOL.INTERNALNO, AOL.WEEKEND,
                   AOL.CATEGORYNO, AOL.SUBCATEGORYNO, Coalesce(AOL.VIRTUALNO, 0) as VIRTUALNO,
                   AOL.APPLYON, AOL.REAL_ALLOTMENT_CLOSE, AOL.QUANTITY
            from AVAIL_ONLINE AOL
            left join AUTO_INC_DATE('03-24-2012', 3) AID
              on 1 = 1
            left join SEASONS_DATES SD
              on SD.CODENAME = AOL.SEASONCODE
              and AID.OUT_DATE between SD.DATEFROM and SD.DATETO
            left join AVAIL_ONLINE_CONDITIONS AOC
              on AOC.INTERNALNO = AOL.INTERNALNO
            left join AVAIL_CALC_TEMP ACT
              on ACT.RANDOMNO    = 1
              and ACT.CATEGORYNO    = AOC.CATEGORYNO
              and ACT.SUBCATEGORYNO = Coalesce(AOC.SUBCATEGORYNO, 0)
              and ACT.INTV_AVAIL    = Coalesce(AOC.AVAILABILITYGRP, 0)
              and ACT.DATE_AVAIL    = AID.OUT_DATE
            left join FIXEDVALUES FX
              on FX.BUILDINGNO = 2
              and AID.OUT_DATE between FX.DATEFROM and FX.DATETO
            where AOL.BUILDINGNO  = 2
            and AOL.CATEGORYNO    = 1
            and ((AOL.DATEFROM   <= '03-26-2012'
                  and AOL.DATETO >= '03-24-2012')
                 or (AOL.SEASONCODE in
                     (Select Distinct(CODENAME) from SEASONS_DATES SD
                      where SD.DATEFROM <= '03-26-2012'
                      and SD.DATETO     >= '03-24-2012'
                      and SD.BUILDINGNO  = 2)))
            and ((AID.OUT_DATE between AOL.DATEFROM and AOL.DATETO) or
                 (AID.OUT_DATE between SD.DATEFROM and SD.DATETO))
            and ((Q_ANTIRELEASE is Null) or
                 (TYPE_Q_ANTIRELEASE = 1 and AID.OUT_DATE >= Current_Date + Q_ANTIRELEASE) or
                 (TYPE_Q_ANTIRELEASE = 2 and AID.OUT_DATE >= Current_Date + (Q_ANTIRELEASE*7)))
            and ((Q_RELEASE is Null) or
                 (TYPE_Q_RELEASE = 1 and AID.OUT_DATE <= Current_Date + Q_RELEASE) or
                 (TYPE_Q_RELEASE = 2 and AID.OUT_DATE <= Current_Date + (Q_RELEASE*7)))
            and ((AOC.LESS_MORE_THAN = 1 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS <= AOC.QUANTITY) or
                 (AOC.LESS_MORE_THAN = 2 and ACT.NUM_OBJECTS - ACT.NUM_LOCKED - ACT.NUM_BOOKINGS >= AOC.QUANTITY))
            and (AOL.WEEKEND = 0 or (AOL.WEEKEND = 1 and Position(Cast(Extract(WeekDay from AID.OUT_DATE-1)+1 as CHAR) in FX.WEEKEND_DAYS) <> 0)))
         group by OUT_DATE, INTERNALNO, WEEKEND, CATEGORYNO, SUBCATEGORYNO,
                  VIRTUALNO, APPLYON, REAL_ALLOTMENT_CLOSE, QUANTITY) Q_SEL
         where NUM_HITS = (Select Count(*) from AVAIL_ONLINE_CONDITIONS AOC2
                           where AOC2.INTERNALNO = Q_SEL.INTERNALNO)
         and Position('2' in Q_SEL.APPLYON) <> 0)
   Group by OUT_DATE, CATEGORYNO, SUBCATEGORYNO, VIRTUALNO, APPLYON) Q_SELB
on Q_SELA.OUT_DATE       = Q_SELB.OUT_DATE
and Q_SELA.CATEGORYNO    = Q_SELB.CATEGORYNO
and Q_SELA.SUBCATEGORYNO = Q_SELB.SUBCATEGORYNO
and Q_SELA.VIRTUALNO     = Q_SELB.VIRTUALNO
and Q_SELA.APPLYON       = Q_SELB.APPLYON
and Q_SELA.WEEKEND       = Q_SELB.WEEKEND
where Q_SELA.OUT_DATE    = Q_SELB.OUT_DATE
and Q_SELA.CATEGORYNO    = Q_SELB.CATEGORYNO
and Q_SELA.SUBCATEGORYNO = Q_SELB.SUBCATEGORYNO
and Q_SELA.VIRTUALNO     = Q_SELB.VIRTUALNO
and Q_SELA.APPLYON       = Q_SELB.APPLYON
and Q_SELA.WEEKEND       = Q_SELB.WEEKEND

:o :o :p :p