0

I have duplicates in my table that I want to select but rather than doing a select distinct on all 12 columns I only want 3 of the columns as distinct. If I do a select distinct on all columns, those 3 columns can still have duplicates that I don't want because some of the other columns are different.

I want to see all columns but want it to be distinct on only 3.

I've tried the group by and with solutions in the question that this one is supposedly a replication of however those did not work. For whatever reason when I do the group by it only executes the query if I include all the columns in the table in it which is not what I want cause I only want the 3 columns distinct. The "with c" just wouldn't execute when I attempted it.

bjbreitling
  • 51
  • 10
  • 1
    `SELECT DISTINCT col1, col2, col3 FROM YourTable`? – Lamak Apr 02 '15 at 15:54
  • How are you going to see the differences in the other columns without repeating the values in the first 3? I think a sample result would help explain what you expect your query to return. – Danny Apr 02 '15 at 15:56

2 Answers2

0

try or something like that ; select distinct from col 1 where Select distinct from col 2 where Select distinct from col 2

haf
  • 21
  • 2
0

Use row_number():

select t.*
from (select t.*, row_number() over (partition by col1, col2, col3
                                     order by (select null)) as seqnum
      from table t
     ) t
where seqnum = 1;

This will choose exacdtly one indeterminate row for each combination of values of the three columns.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So I submitted this (I know that the where statement works in the sql I have) select t.* from (select t.*, row_number() over (partition by Plant, Timestamp, FermBatchNumber order by (select null)) as seqnum FROM dbo.FermKineticsNIR WHERE t.\!"Timestamp\!" >= DATEADD(d, -30, getdate()) ) t where seqnum = 1; and it doesn't execute the query so I have something wrong probably. Can you tell what might be up? – bjbreitling Apr 02 '15 at 18:08
  • @bjbreitling . . . If the query doesn't execute, then SQL Server should give you an error message indicating the problem. – Gordon Linoff Apr 02 '15 at 22:06
  • Thanks! I was submitting the query throughanother software to ms sql so I had to do some modifications but now it works great! If I had a higher rep I'd give this a vote! – bjbreitling Apr 03 '15 at 14:42