J'ai une table SQL avec les colonnes: ID, ADate, XValue et Type. Pour chaque valeur "Type", j'aimerais récupérer la ligne la plus récente (par ADate), la ligne la moins récente (par ADate) et aussi la ligne contenant la valeur "XValue" la plus élevée.

Est-il possible d'être exécuté via une seule requête SQL? J'ai essayé plusieurs fois sans succès.

2
Stefano Guimarães Falce 3 mai 2017 à 23:49

3 réponses

Meilleure réponse
select ID, ADate, XValue,[Type]
from (
  select *
     ,row_number() over (partition by type order by adate asc) AS DateSeq
     ,row_number() over (partition by type) AS TypeCount
     ,row_number() over (partition by type order by xvalue desc) AS ValueSeq
  from t
  ) as tt
where tt.DateSeq=1 OR tt.DateSeq=tt.TypeCount OR tt.ValueSeq=1
1
Nolan Shang 3 mai 2017 à 21:27

En utilisant row_number() vous pourrait faire quelque chose comme ça:

select ID, ADate, XValue and Type
from (
  select *
    , min_adate  = row_number() over (partition by type order by adate asc)
    , max_adate  = row_number() over (partition by type order by adate desc)
    , max_xvalue = row_number() over (partition by type order by xvalue desc)
  from t
  ) as sub
where 1 in (min_adate,max_adate,max_xvalue)
/* --also could be written as 
where min_adate = 1
   or max_adate = 1
   or max_xvalue = 1
*/

L'utilisation d'une expression de table commune peut la rendre plus lisible.

;with cte as (
  select *
    , min_adate  = row_number() over (partition by type order by adate asc)
    , max_adate  = row_number() over (partition by type order by adate desc)
    , max_xvalue = row_number() over (partition by type order by xvalue desc)
  from t
)
select ID, ADate, XValue and Type
from cte
where 1 in (min_adate,max_adate,max_xvalue)
2
SqlZim 3 mai 2017 à 21:01

Une méthode utilise row_number():

select t.*
from (select t.*,
             row_number() over (partition by type order by aDate desc) as seqnum_adate_desc,
             row_number() over (partition by type order by aDate asc) as seqnum_adate_asc,
             row_number() over (partition by type order by XValue desc) as seqnum_xvalue_desc
      from t
     ) t
where 1 in (seqnum_adate_desc, seqnum_adate_asc, seqnum_xvalue_desc);
1
Gordon Linoff 3 mai 2017 à 21:03