Subquier SQL simple

Vous pouvez utiliser un Row_Number () code> dans la sous-requête:

SELECT c.id as id, c.LANG as Language, c.ALLOWEMAIL as AllowEmail,
       l.CONFIRMED as confirmed
FROM cus.CUSTOMER c JOIN
     (SELECT s.*,
             ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY s.timestamp DESC) as seqnum
      FROM settings s
     ) s
     ON s.id = c.id AND s.seqnum = 1;

Vous pouvez utiliser des fonctions analytiques. Max () Over (Partition par) La clause CODE> peut vous donner une identification maximale horodatale.

Fonctions analytiques DOCS 11GR2 P>

SELECT SECONDD.CONFIRMED
FROM CUSTOMER CU
   INNER JOIN
   (SELECT 
           *
      FROM (SELECT SECONDD.*,
                   MAX (S.TIMESTAMP) OVER (PARTITION BY S.ID)
                      AS MAXTIMESTAMP
              FROM SETTINGS SECONDD)
     WHERE TIMESTAMP = MAXTIMESTAMP) SECONDD
      ON SECONDD.ID = CU.ID

Ne t'inquiète pas; Bien que cela sonne très basique, ce n'est pas le cas: -)

Le moyen le plus simple d'obtenir la confirmation du dernier horodatage dans Oracle est garder la dernière code>. EG: P>

CREATE OR REPLACE VIEW cus_settings as
SELECT 
  c.id AS id,
  c.lang AS language,
  c.allowemail AS allowemail,
  (
    SELECT MAX(confirmed) KEEP (DENSE_RANK LAST ORDER BY timestamp)
    FROM settings s
    WHERE s.customer_id = c.id
  ) AS confirmed
FROM cus.customer c;