CODEWITHSUNDEEP
sqlsql-serverssis
Manoj Nayak
Manoj Nayak

Reputation: 2509

Take the versions other than Max SQL

I have a Table with 4 columns. I want to retrieve the highest version for the combination of Number,Code and Serial No. If there are multiple versions the remaining versions I need another result set.

Number  Version Code    Serial No
12345   1        VB      88
12345   2        VB      88
23456   1        VB      44
33334   1        VB      55
33334   2        VB      55
33334   3        VB      55

In One table I need only highest issues for each Number,Code,Serial No. My result1

Number  Version Code    Serial No
12345   2        VB      88
23456   1        VB      44
33334   3        VB      55

Another result set 

Number  Version Code    Serial No
12345   1        VB      88
33334   1        VB      55
33334   2        VB      55

Anyway I can get the first set using the query. How can get the second set. Without using temp table.

Upvotes: 1

Views: 69

Answers (4)

SqlZim
SqlZim

Reputation: 38023

common table expression with row_number():
change cte.rn=1 to cte.rn!=1 to get the second result set

with cte as (
  select   
      t.*
    , rn = row_number() over (
            partition by t.[Number], t.[Code], t.[Serial No]
            order by t.[Version] desc
            )
    from tbl t
)
select cte.*
  from cte
  where cte.rn = 1

top with ties:

select top 1 with ties
    t.*
  from tbl t
  order by row_number() over (
    partition by t.[Number], t.[Code], t.[Serial No] 
    order by t.[Version] desc)

second result set:

select t.*
from tbl t

except 

select top 1 with ties
    t.*
  from tbl t
  order by row_number() over (
    partition by t.[Number], t.[Code], t.[Serial No] 
    order by t.[Version] desc)

inner join on max(version):
change m.[Version] = t.[Version] to m.[Version] != t.[Version] to get the second result set

select t.* 
  from tbl t
    inner join (
      select m.[Number], m.[Code], m.[Serial No], Version = max(m.[Version])
      from tbl m
      group by m.[Number], m.[Code], m.[Serial No]
      ) 
      on m.[Number] =  t.[Number]
        and m.[Code] =  t.[Code]
        and m.[Serial No] = t.[Serial No]
        and m.[Version] = t.[Version]

common table expression with max([Version]) over() version:
change m.[Version] = t.[Version] to m.[Version] != t.[Version] to get the second result set

with cte as (
  select 
       t.*
    , MaxVersion = max([Version]) over (
            partition by t.[Number], t.[Code], t.[Serial No]
            )
    from tbl t
)
select cte.[Number], cte.[Code], cte.[Serial No], cte.[Version]
  from cte
  where cte.MaxVersion = cte.[Version]

Upvotes: 2

Shidai
Shidai

Reputation: 227

If you have the query to get MAX(VERSION) already, it is logical to use it as a subquery.

MAX(VERSION): 

SELECT NUMBER,CODE,SERIAL,MAX(VERSION) 
    FROM Q
    GROUP BY NUMBER,CODE,SERIAL

The rest:

SELECT NUMBER,CODE,SERIAL,VERSION 
    FROM Q
    WHERE (NUMBER,CODE,SERIAL,VERSION) NOT IN (
        SELECT NUMBER,CODE,SERIAL,MAX(VERSION) 
            FROM Q
            GROUP BY NUMBER,CODE,SERIAL 
    )

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the row_number function to get both the results, changing the where condition.

select number,version,code,serialno
from (select t.*
      ,row_number() over(partition by number,code,serialno order by version desc) as rnum
      from tablename t
     ) x
where rnum > 1 --rnum=1 for the first result

Upvotes: 2

Utsav
Utsav

Reputation: 8093

TO get second result set, use something like below. Didn't get to run the query

select * from 
(select t.*,
    row_number() over (partition by number,code,SerialNo order by version desc) as rnk
from table t) 
where rnk <> 1

Upvotes: 2

Related Questions