remove duplicate values of only one column value from all the available columns in sql query

Question

I have a sql query with three columns .I want to remove any duplicate value exits in beam_current column.How to do so.I work in sql-server2012

I used Distinct but then also I'm getting duplicate values of beam_current. My sql Query is-

select DISTINCT (beam_current), logtime, beam_energy 
from INDUS2_BDS.dbo.DCCT 
where logtime between '2014-08-09 01:13:03' and '2014-08-09 02:16:53'
      and (beam_current like '%9.96' 
           or beam_current like '%9.97' 
           ... etc ...) 
      and beam_energy between '550' and '552'

EDIT-1 My output is-

In first column 29.98 is repeating thrice.I only want any one of the row corresponding to 29.98.How to do that??

Answer 1

The distinct keyword works on the entire row (all columns), so:

select DISTINCT (beam_current), logtime, beam_energy 

is the same as:

select DISTINCT beam_current, logtime, beam_energy 

is the same as:

select DISTINCT ((beam_current)), (logtime), (((((beam_energy)))))

You can use row_number() to select only the latest row per value of beam_energy:

select  *
from    (
        select  row_number() over (
                    partition by beam_current
                    order by logtime desc) as rn
        ,       *
        from    INDUS2_BDS.dbo.DCCT 
        where   logtime between '2014-08-09 01:13:03' and '2014-08-09 02:16:53'
                and (beam_current like '%9.96' 
                    or beam_current like '%9.97' 
                    ... etc ...) 
                and beam_energy between '550' and '552'
        ) numbered_rows
where   rn = 1 -- Latest row per beam_current