Skip to content
Advertisement

Display null if only one value for Min(Column) and Max(column) SQL query

This is my original table

ID        value    value1    Value3 
-----------------------------------
  123     45789    null       5648
  234     45789    5468      null
12453      5846    589745    null
21536      5846    null      54689
            
            

Desired output:

ID        value     value1    Value3        ID        value     value1    Value3    
-------------------------------------------------------------------------
  123     45789     null      5648            234     45789     5468        null
12453      5846     589745    null          21536      5846     null    54689
            
            

Current output with my query

ID        value     value1    Value3    ID        value     value1     value3
---------------------------------------------------------------------------------
  123     45789      5468      5648       234     45789       5468      5648
12453      5846    589745     54689     21536      5846     589745     54689

Query:

select 
    min(ID) as ID, min(value) as value, min(value1) as value1,
    max(ID) as ID, max(value) as value, max(value1) as value1
from 
    table1

Advertisement

Answer

You can get your expected output from your sample input:

select
  T1.ID,
  T1.value,
  T1.value1,
  T1.value3,
  T2.ID,
  T2.value,
  T2.value1,
  T2.value3
from Table1 T1
join Table1 T2
  on T1.value = T2.value
  and T1.ID < T2.ID
;

See it in action: SQL Fiddle You should probably add some aliases to at least one set of columns.

Not sure, though, how you got two rows of data with your sample query.

Please comment, if and as this requires adjustment / further detail.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement