September 23, 2009 at 3:26 pm
Hi
I have a table ,Table1 - with col's - ID,Name,desc. If there a duplicate name I am trying to get the record with higher id....any help.
Thanks.
September 23, 2009 at 3:34 pm
If the following code doesn't resolve your issue you should read and follow the article referenced in my signature. This would help us to better understand your requirement.
SELECT max(ID),Name
FROM Table1
GROUP BY Name
September 23, 2009 at 9:51 pm
For low volume queries, you can also use a cte with ROWNUMBER() using both partition and order by, then testing for where rownumber = 1.
Look up the syntax for ROWNUMBER() in Books Online (BOL). Search "RowNumber" on here and you might even find some examples.
I agree with Lutz. Post more specific data and questions and you will get more specific answers than the intentionally vague (but correct) answer I just posted.
Read the article[/url] he suggested.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 24, 2009 at 7:00 am
Curious - you mention for "low-volume" queries. What would you define as low-volume, and what solution would you employ for larger volume queries? Some form of partitioning the larger query into subsets then using row_number against the subsets?
September 24, 2009 at 11:23 am
Where row_number() ceases to be the most efficient technique depends on a number of variables, like the row size being retrieved and the existence of index. The last time I tested, it was using row number against a summary query that got the max value for each grouping and then pulled the entire row that matched the summary row values. In that case, at 10,000 rows RowNumber was the winner. But approaching a million rows, the summary query technique pulled ahead. Row_Number relies on a sort and after a while, that sort becomes a lot of work.
I can't give you a hard and fast rule that says "Always use RowNumber()" or "Don't use RowNumber()" or even one that says "Never use RowNumber over 10000 rows." Everything depends. Some techniques scale better than others, but often you don't know where the breaking point will be. So you have to test, and think about how big the source table(s) might get when choosing a technique. Don't get me wrong. Row_number is a quick, clean technique that I have employed since I first learned about it. But when high volume is a possibility, I will consider and test other options.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply