0

Is there a way to convert this query into one without having the subquery in where clause?

      select distinct 
          ie.install_id
        , ie.sp_id
        , ie.device_config_id
        from d1_sp sp
        inner join install_evt ie  
            on ie.sp_id = sp.sp_id
        where ie.install_dttm = (select max(iemax.install_dttm)
                                    from install_evt iemax
                                    where iemax.sp_id = sp.sp_id)
Punter Vicky
  • 15,954
  • 56
  • 188
  • 315
  • 3
    You could use a nested query or a cte with a window aggregate. What is the problem you are trying to solve and what is the database? – Stu Sep 09 '21 at 18:00
  • 1
    . [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Sep 09 '21 at 18:13
  • @Stu I’m trying to convert this using spark api query. I wasn’t warble you find a straightforward match for a sub query in where clause. So I was trying to see if this query could be modified or broken down into multiple queries so that I can update my spark data frame query. – Punter Vicky Sep 09 '21 at 18:42

1 Answers1

1
      select distinct 
          ie.install_id
        , ie.sp_id
        , ie.device_config_id
        , max(ie.install_dttm)
        from d1_sp sp
        inner join install_evt ie  
            on ie.sp_id = sp.sp_id
group by           ie.install_id, ie.sp_id, ie.device_config_id

if you need additional grouping you can use an order by

SCCJS
  • 96
  • 8