Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Top 10 Query Expand / Collapse
Author
Message
Posted Monday, May 13, 2013 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:56 AM
Points: 15, Visits: 68
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!
Post #1452079
Posted Monday, May 13, 2013 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 13,080, Visits: 11,916
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1452085
Posted Monday, May 13, 2013 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:56 AM
Points: 15, Visits: 68
BOOM! Thanks Sean Lange. Sorted.
Post #1452089
Posted Monday, May 13, 2013 8:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 13,080, Visits: 11,916
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1452119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse