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

distinct-valuessql-server

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-

enter image description here

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

Best Solution

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
Related Question