May 13, 2013 at 7:00 am
Hi All,
Got a quick Select Top 10 question. I'm running a simple query to find top ten people who have contacted us in the past month.
The problem is that it's only counting the top 10 distinct people, I want to include rows 11-13 in my count as these people have contated the same amout of times as Cust20 in Row 10.
Row Count Cust ID
1 64 Cust11
2 24 Cust12
3 21 Cust13
4 16 Cust14
5 14 Cust15
6 12 Cust16
7 11 Cust17
8 11 Cust18
9 11 Cust19
10 10 Cust20
11 10 Cust21
12 10 Cust22
13 10 Cust23
Hope that makes sense!
May 13, 2013 at 7:09 am
justfillingtime (5/13/2013)
Hi All,Got a quick Select Top 10 question. I'm running a simple query to find top ten people who have contacted us in the past month.
The problem is that it's only counting the top 10 distinct people, I want to include rows 11-13 in my count as these people have contated the same amout of times as Cust20 in Row 10.
Row Count Cust ID
1 64 Cust11
2 24 Cust12
3 21 Cust13
4 16 Cust14
5 14 Cust15
6 12 Cust16
7 11 Cust17
8 11 Cust18
9 11 Cust19
10 10 Cust20
11 10 Cust21
12 10 Cust22
13 10 Cust23
Hope that makes sense!
You can use "WITH TIES".
Select top 10 WITH TIES [Columns]
From YourTable
Order by Something
_______________________________________________________________
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/
May 13, 2013 at 7:14 am
BOOM! Thanks Sean Lange. Sorted.
May 13, 2013 at 8:05 am
justfillingtime (5/13/2013)
BOOM! Thanks Sean Lange. Sorted.
You're welcome. Glad that worked for 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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply