convert a subquery to over

Question

You can convert this query for example to optimize over for a consultation, is to get you up depending on the version, it's the same table.

select MAX(y.Version),
 y.Unidad,
 y.RutCompañia,
 y.Cobertura,
 y.Temporada,
 y.PorcentajeSubsidio,y.RendimientoInferior,y.RendimientoSuperior 
from ddbb.dbo.NS_CA y 
where y.IDVariedad='010101' and y.Temporada ='2011'
and y.ZHS='CA0607'
and y.Moneda='UF'
and y.Version in (select MAX(x.Version) from ddbb.dbo.NS_CA  x where x.IDVariedad='010101' and x.Temporada ='2011'
and x.ZHS='CA0607'
and x.Moneda='UF')
group by y.Unidad,y.RutCompañia,y.Cobertura,y.Temporada,y.PorcentajeSubsidio,y.RendimientoInferior,y.RendimientoSuperior

I imagined something like this

select  MAX(b.Version) OVER(PARTITION BY b.IDVariedad,b.IDRubro ) as maximo

but it does not work properly

Thank you.

EDIT:

Thanks for you translate and answers. Add more info , by example i have next table (tabla) :

| Version    | Temporada   | Unidad       | etc          |
|:-----------|------------:|:------------:|:------------:|
| 00         |        2011 |    N         |  xx          |
| 00         |        2011 |    N         |  xx          |
| 01         |        2011 |    N         |  xx          |
| 02         |        2011 |    N         |  xx          |
| 03         |        2011 |    N         |  xx          |
| 03         |        2011 |    N         |  xx          |

and query i would generate is :

select * from tabla a
where a.version in (select max(b.Version) from tabla b where b.Temporada='2011')


    | Version    | Temporada   | Unidad       | etc          |
    | 03         |        2011 |    N         |  xx          |
    | 03         |        2011 |    N         |  xx          |

is possibility change sub-query to 'over partition' ? , thanks

Please translate your question to English. If the language you're speaking is Portuguese, check out http://pt.stackoverflow.com/ — Jeroen Vannevel, Oct 02 '14 at 21:04
I moved the SQL SERVER 2012 from the title to the tags. But it does not look like SQL server to me, please adjust correspondingly — Sebas, Oct 02 '14 at 23:17

score 0 · Answer 1 · answered Oct 02 '14 at 23:36

0

WITH cte as
(SELECT ROW_NUMBER() OVER (ORDER by y.Version DESC) row_id
    ,   y.Unidad
    ,   y.RutCompañia
    ,   y.Cobertura
    ,   y.Temporada
    ,   y.PorcentajeSubsidio
    ,   y.RendimientoInferior
    ,   y.RendimientoSuperior
FROM ddbb.dbo.NS_CA y
WHERE y.IDVariedad = '010101'
    AND y.Temporada = '2011'
    AND y.ZHS = 'CA0607'
    AND y.Moneda = 'UF'
    AND y.Version)


SELECT * FROM cte
where row_id = 1

answered Oct 02 '14 at 23:36

,apparently did not express properly what I'm looking for is to extract the maxima 'Version' of a specific year and other combinations, thanks. – waflessnet Oct 03 '14 at 13:53
Use the select query with row_id =1. That should give you the max version without the use of the sub query – Oct 05 '14 at 03:13

score 0 · Answer 2 · edited May 23 '17 at 12:05

Seems you want this:

select MAX(y.Version),
 y.Unidad,
 y.RutCompañia,
 y.Cobertura,
 y.Temporada,
 y.PorcentajeSubsidio,y.RendimientoInferior,y.RendimientoSuperior 
from y.Version =
     (select MAX(x.Version) 
        from ddbb.dbo.NS_CA  x 
       where x.IDVariedad=y.IDVariedad and x.Temporada = y.Temporada
             and x.ZHS=y.ZHS   and x.Moneda=y.Moneda)
group by y.Unidad,y.RutCompañia,y.Cobertura,y.Temporada,y.PorcentajeSubsidio,y.RendimientoInferior,y.RendimientoSuperior

See Why no windowed functions in where clauses?

convert a subquery to over

2 Answers2