not getting the desired output

Question

not getting the desired output

abmore

Hall of Fame

Points: 3628
More actions
October 19, 2007 at 4:13 am

#61391

if object_id('tempdb..#tmpExample') is not null
drop table #tmpExample
create table #tmpExample
(rowidtinyintidentity(1,1),
col1_refidtinyint,
col1_valvarchar(10),
col2_valvarchar(10),
col3_valvarchar(10),
col4_valvarchar(10)
)
insert#tmpExample
(col1_refid, col1_val, col2_val, col3_val, col4_val )
select 1, 'A', 'B', 'C', 'D'
union
select 1, 'E', 'F', 'G', 'H'
union
select 1, '1-A', '2-F', '3-C', '4-H'
union
select 2, 'J', 'K', 'I', 'O'
union
select 2, '1-J', '1-K', '1-I', '1-O'
union
select 3, 'M', 'N', 'E', 'D'
union
select 3, 'E-3', 'F-4', 'G-5', 'H-5'
union
select 4, 'P', 'K', 'I', 'O'
union
select 4, '1-R', '1-P', '1-W', '1-O'
union
select 5, 'MW', 'NQ', 'EV', 'DS'
select * from #tmpExample order by col3_val
if object_id('tempdb..#tmpExample') is not null
drop table #tmpExample
Output Required
===================
distinct col1_refid.
but the criteria is i want to pass order by clause dynamically and i want the distinct col1_refid. in sorted order, whtever sort criteria i'll send.
e.g
select top 100 percent col1_refid
from #tmpExample order by col1_val, col2_val
Like this.....
Col_refid
---------
1
2
4
3
5

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Are you using SQL Server 2005? If so, please don't cross-post in the SQL Server 2005 forum. If not, please don't cross-post in this forum.

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

You need to use dynamic SQL. Make sure you understand the security consequences of doing this. Your query will look something like this:

DECLARE @MyCol nvarchar(28)

DECLARE @sql nvarchar(400)

SET @MyCol = 'colname'

SET @sql = 'SELECT col1, col2 FROM MyTable ORDER BY ' + @Mycol

EXEC @sql

John

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Abhijit Just make sure you use

EXEC(@sql)

OR EXEC sp_executesql @sql

in the above syntax.

Prasad Bhogadi
www.inforaise.com

abmore Hall of Fame Points: 3628 More actions · Answer 4

thxs John, but in output result i want only distinct col_ref [ whatever may be the order by clause colname]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

OK, so you have to tweak your "@sql=" line so that it inserts @MyCol in the middle as well as at the end. But I'm sure I don't need to tell you how to do that.

John

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

Abhijit, Hope you got what John meant, you need to have your dynamic order by column as an item your SELECT statement as you want to retrieve DISTINCT rows grouped by col1_refid . So as a fundamental you should have the columns used to sort using ORDER BY clause as part of the SELECT Criteria.

Also based on your test data I don't think you would get any distinct rows because the data in others columns is very unique.

Prasad Bhogadi
www.inforaise.com