ansaurus

Question

Answer 1

+1 A:

How about:

Select id, empno, empno2
from employeestable left outer join (
 SELECT min([id]) as minid
      ,[empno] empno2
  FROM [EmployeesTable]
group by empno) etab2 on employeestable.id = etab2.minid

You're saying a subquery won't work, though - why not?

Tobiasopdenbrouw 2010-07-21 08:55:29

This might work. But empno2 still returns the same number of rows as empno, now the solution am looking for is to make empno2 return only distinct values of empno. any help on that.

Name.IsNullOrEmpty 2010-07-21 09:17:21

What you want, is what the above query does (plus NULLS). You cannot have empno2 return less rows: all columns have the same number of rows in a query. Edit: I don't know SSRS, but can't you just use the subquery I have in my answer as the source of your dropdown?

Tobiasopdenbrouw 2010-07-21 09:28:33

the problem is that the parameter dropdown has got to pick from the columns returned by the main query(Report Query) as SSRS 2005 does not support multiple datasets in a single report.When specifiying the source of the dropdown, i can only specify a column from the columns returned by the report query. __________Am able to do that at the moment but now the dropdown has duplicate options of the same value.

Name.IsNullOrEmpty 2010-07-21 09:36:27

What happens when you try the query I posted? Do you get a dropdown box with a bunch of NULL lines? That seems like a rather interesting drawback in SSRS, which an SSRS expert should be able to help you with.

Tobiasopdenbrouw 2010-07-21 09:45:05

Tobiasopdenbrouw, when i run your query, i get all records(including duplicates). would love to remove the duplicates.

Name.IsNullOrEmpty 2010-07-21 09:51:42

I see you've added a sample return set, where id is not autoincrementing int (which I was expecting). Still: your expected result set has some results in empno2, the remainder is nulls. Is it really important for SSRS that the results be at the top of the column? In that case, an ORDER BY or smarter query may solve your problems.

Tobiasopdenbrouw 2010-07-21 10:13:52

Answer 2

A:

Your requirement is not clear and I also have very little information. Following is what you need. This can be even better but it is just a try.

declare @temp table
(
    uniqueid int identity(1, 1),
    id int,
    empno varchar(50),
    empno2 varchar(50)
)

insert into @temp  select   1,  '0T4/HR', null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1 , '0T4/HR' , null
insert into @temp select     1,  '0T4/HR' , null
insert into @temp select     1,  '0T4/HR'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E' , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     2,  '2VP/E'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null
insert into @temp select     3,  'xT9/67'  , null
insert into @temp select     3,  'XT9/67'  , null

SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, empno into #temp FROM @temp group by empno, id

update @temp set empno2 = t2.empno
from @temp t inner join #temp t2 on t.uniqueid = t2.id

select * from @temp

drop table #temp

Muhammad Kashif Nadeem 2010-07-21 14:17:14

ansaurus

tags:

views:

answers:

"Distinct" column in SQL query

related questions