March 13, 2012 at 9:52 am
Hi Friends,
i wrote a SQL statement with row number ()(to get the first count) in the excel source under SQL command. But it is not supporting..
how can we do this ? Is there any alternate way ?
Thanks,
Charmer
March 13, 2012 at 10:21 am
Wow you really are frantic today. Slow down and ask your questions with enough details so somebody else can help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2012 at 3:12 am
Sean Lange (3/13/2012)
Wow you really are frantic today. Slow down and ask your questions with enough details so somebody else can help you.
I am really sorry Sean...
with cte as (select row number () over(partition by column order by column) as count , * from table ) select * from cte where count = 1
Actually, this is the Query i used in the Excel source...but it seems excel does not support row number .....
ID StreetName Type
1 Wells A
2 S Wellls B
3 N Wells B
2 E Wells B
duplicates in ID column....i need any one row with ID = 2...not both rows..
this is why i used this SQL statement..
is there any alternative for this?
Thanks,
Charmer
March 14, 2012 at 3:56 am
Excel does not support the entire TSQL syntax.
If the number of rows isn't that big you can use the SORT component to delete the duplicates for you.
If performance is an issue, dump the data of the Excel file in a staging table and execute your row_number() query on that one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 5:45 am
Koen Verbeeck (3/14/2012)
Excel does not support the entire TSQL syntax.If the number of rows isn't that big you can use the SORT component to delete the duplicates for you.
If performance is an issue, dump the data of the Excel file in a staging table and execute your row_number() query on that one.
Thanks Koen..It worked out..with sort component...
Thanks,
Charmer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply