October 19, 2007 at 4:13 am
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
October 19, 2007 at 4:22 am
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
October 19, 2007 at 4:29 am
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
October 19, 2007 at 5:34 am
Abhijit Just make sure you use
EXEC(@sql)
OR EXEC sp_executesql @sql
in the above syntax.
Prasad Bhogadi
www.inforaise.com
October 19, 2007 at 5:40 am
thxs John, but in output result i want only distinct col_ref [ whatever may be the order by clause colname]
October 19, 2007 at 5:47 am
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
October 19, 2007 at 6:04 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply