Unique values only

Question

I am trying to retrieve all the unique set of values for this schema

CREATE TABLE #MyTable (FldID int,DataVal1 varchar(7), DataVal2 char(1))

INSERT INTO #MyTable  VALUES(1,'6005001','N')
INSERT INTO #MyTable  VALUES(1,'6005002','N')
INSERT INTO #MyTable  VALUES(1,'6006001','N')
INSERT INTO #MyTable  VALUES(1,'6006002','N')
INSERT INTO #MyTable  VALUES(1,'8955002','N')
INSERT INTO #MyTable  VALUES(2,'6005001','N')
INSERT INTO #MyTable  VALUES(2,'6005002','N')
INSERT INTO #MyTable  VALUES(2,'6006001','N')
INSERT INTO #MyTable  VALUES(2,'6006002','N')
INSERT INTO #MyTable  VALUES(2,'8955002','N')
INSERT INTO #MyTable  VALUES(3,'8941000','N')

I am expecting a result like this

Set1 6005001 N
Set1 6005002 N
Set1 6006001 N
Set1 6006002 N
Set1 8955002 N
Set2 8941000 N

Thanks in advance.

Answer 1

Can you explain what you want to do?  I don't quite see the pattern, especially the Set1 and Set2 thing...

Answer 2

I would like to retrieve all the unique set of values

In my example I have 3 sets of data (by first column)

Set1
1,'6005001','N'
1,'6005002','N'
1,'6006001','N'
1,'6006002','N'
1,'8955002','N'
Set 2
2,'6005001','N'
2,'6005002','N'
2,'6006001','N'
2,'6006002','N'
2,'8955002','N'
and finally Set 3
3,'8941000','N'

I would like to retrieve unique sets. In this example Set 2 and Set 1 are same, so I would expect Set 1 records and Set 3 records as output.

If I can retrieve the information as follows it will work fine for me.

1,'6005001','N'
1,'6005002','N'
1,'6006001','N'
1,'6006002','N'
1,'8955002','N'
3,'8941000','N'

Let me know if you need more details.

Answer 3

Code Snippet

select FldID,DataVal1,DataVal2
from (select FldID,DataVal1,DataVal2,
      min(FldID) over(partition by DataVal1,DataVal2) as minFldID
        from t) a
 where FldID = minFldID;

 

 

 

I used OLAP
However I did not test above.

Answer 4

Try:

 

select min(FldID) min_FldID, DataVal1, DataVal2

from #MyTable

group by DataVal1, DataVal2

order by min_FldID, DataVal1, DataVal2

 

-- or

 

select dense_rank() over(order by min(FldID)) as set_number, DataVal1, DataVal2

from #MyTable

group by DataVal1, DataVal2

order by set_number, DataVal1, DataVal2

GO

 

 

AMB

Answer 5

Thanks for the replies. But, this code is not working if have additional data like this

INSERT INTO #MyTable  VALUES(4,'6005001','N')
INSERT INTO #MyTable  VALUES(4,'6005002','N')
INSERT INTO #MyTable  VALUES(4,'6006001','N')
INSERT INTO #MyTable  VALUES(4,'6006002','N')
INSERT INTO #MyTable  VALUES(4,'8955003','N')

In this case this set has first 4 records as 1 and 2 but the results are not what I am expecting

This is what I was expecting
Set1 6005001 N
Set1 6005002 N
Set1 6006001 N
Set1 6006002 N
Set1 8955002 N
Set2 8941000 N
Set3 6005001 N
Set3 6005002 N
Set3 6006001 N
Set3 6006002 N
Set3 8955003 N

Note: as the last record is different this is a different set.

Answer 6

Wow this is a really interesting question.
I solved this.
However I used Oracle10g.
But I expect that SQLServer experts of this forum will rewrite my query.

 

Code Snippet

create table MyTable(FldID,DataVal1,DataVal2) as
select 1,'6005001','N' from dual union all
select 1,'6005002','N' from dual union all
select 1,'6006001','N' from dual union all
select 1,'6006002','N' from dual union all
select 1,'8955002','N' from dual union all
select 2,'6005001','N' from dual union all
select 2,'6005002','N' from dual union all
select 2,'6006001','N' from dual union all
select 2,'6006002','N' from dual union all
select 2,'8955002','N' from dual union all
select 3,'8941000','N' from dual union all
select 4,'6005001','N' from dual union all
select 4,'6005002','N' from dual union all
select 4,'6006001','N' from dual union all
select 4,'6006002','N' from dual union all
select 4,'8955003','N' from dual;

 

 

 

Code Snippet

col "Set" for a10

 

select 'Set' || to_char(dense_rank() over(order by FldID)) as "Set",
DataVal1,DataVal2
from (select FldID,DataVal1,DataVal2,
      count(*) over(partition by DataVal1,DataVal2
      order by FldID range 1 preceding) as cnt
        from MyTable) a
 where cnt =1
order by FldID,DataVal1;

Set   DATAVAL  D
----  -------  -
Set1  6005001  N
Set1  6005002  N
Set1  6006001  N
Set1  6006002  N
Set1  8955002  N
Set2  8941000  N
Set3  6005001  N
Set3  6005002  N
Set3  6006001  N
Set3  6006002  N
Set3  8955003  N

 

 

without count of OLAP version.

 

Code Snippet

select 'Set' || to_char(dense_rank() over(order by FldID)) as "Set",
DataVal1,DataVal2
  from MyTable a
 where not exists(select 1 from MyTable b
                   where b.DataVal1 = a.DataVal1
                     and b.DataVal2 = a.DataVal2
                     and b.FldID = a.FldID -1)
order by FldID,DataVal1;

 

 

 

Answer 7

Perfect. Thanks you so much.