SELECT TOP 10 actually selects top 12...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT TOP 10 actually selects top 12...

Author

Topic

chien_fu
Starting Member

16 Posts

Posted - 2007-11-13 : 23:03:06

This is my statement:

SELECT TOP 10 * FROM Team_Roster ORDER BY P_Goals DESC

This is what I get:

Place Number Player Name Team Year Position Goals
1 44 Tyler, Bret Brunswick 2004 Webmaster 17
2 29 House, Tanner Bonny 0 5
3 25 Dee, Robby Cheverus 0 4
4 7 Marshall, Jeffrey Cheverus 0 3
5 27 Ramsey, Travis Biddeford 0 3
6 3 Thompson, Joe Brunswick 1960 Wired 3
7 5 VanDyk, Josh Cheverus 0 2
8 3 Dimmen, Jeff Cheverus 0 2
9 79 Danis-Pepin, Simon Cheverus 0 2
10 45 Oinker, Jeremy Biddeford 1999 Wonderboy 2
11 98 Good, Wicked Brunswick 2007 Lefty 2
12 21 Duffy, Matt Biddeford 0 2

What the heck is going on?
See the following list of: rows desired vs. rows returned

SELECT TOP n

When n=1, actual number of rows returned = 1 etc.
" 2 " " 2
3 3
4 12
5 12
6 12
7 12
8 12
9 12
10 12
11 12
12 12
13 16
14 16
15 16
16 16
17 27

Any ideas?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 23:24:20

[code]
create table #teamroster (
place int not null,
[number] int not null,
[name] varchar(40) not null,
[Year] int not null,
[Team] varchar(25) not null,
position char(25) null,
[Goals] int not null)

Insert into #teamroster (
place,number,name,team,year,position,goals)
Select 1, 44, 'Tyler, Bret', 'Brunswick', 2004 ,'Webmaster', 17 UNION ALL
Select 2, 29, 'House, Tanner', 'Bonny', 0,NULL, 5 UNION ALL
Select 3, 25, 'Dee, Robby' ,'Cheverus' ,0,NULL, 4 UNION ALL
Select 4, 7, 'Marshall, Jeffrey', 'Cheverus', 0,NULL, 3 UNION ALL
Select 5, 27, 'Ramsey, Travis' ,'Biddeford' ,0 ,NULL,3 UNION ALL
Select 6, 3, 'Thompson, Joe' ,'Brunswick', 1960,' Wired', 3 UNION ALL
Select 7, 5, 'VanDyk, Josh' ,'Cheverus', 0 ,NULL,2 UNION ALL
Select 8, 3, 'Dimmen, Jeff' ,'Cheverus', 0 ,NULL,2 UNION ALL
Select 9, 79, 'Danis-Pepin', 'Simon Cheverus' ,0,NULL, 2 UNION ALL
Select 10, 45, 'Oinker, Jeremy' ,'Biddeford' ,1999, 'Wonderboy', 2 UNION ALL
Select 11, 98, 'Good, Wicked' ,'Brunswick' ,2007 ,'Lefty', 2 UNION ALL
Select 12, 21, 'Duffy, Matt' ,'Biddeford' ,0 ,NULL,2

Select Top 10 * from #teamroster
[/code]

I only get 10 results.

My guess is your team roster is a view with a join that is returning too many records.

Poor planning on your part does not constitute an emergency on my part.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-14 : 01:52:53

create table testing(place int,
number int,
player varchar(12),
[name] varchar(15),
team varchar(15),
[year] int,
position varchar(14),
goals int)
insert into testing values(1,44,'Tyler','Bret','Brunswick',2004,'Webmaster',17 )
insert into testing values(2, 29,'House', 'Tanner','Bonny',0,null,5 )
insert into testing values(3, 25, 'Dee', 'Robby', 'Cheverus',0,null,4 )
insert into testing values(4, 7, 'Marshall', 'Jeffrey','Cheverus',0,null,3)
insert into testing values(5, 27, 'Ramsey', 'Travis', 'Biddeford' ,0,null,3)
insert into testing values(6, 3, 'Thompson', 'Joe', 'Brunswick', 1960, 'Wired', 3 )
insert into testing values(7, 5, 'VanDyk', 'Josh', 'Cheverus' ,0 ,null,2)
insert into testing values(8, 3, 'Dimmen', 'Jeff', 'Cheverus', 0, null, 2 )
insert into testing values(9, 79, 'Danis-Pepin', 'Simon', 'Cheverus' ,0,null ,2 )
insert into testing values(10, 45, 'Oinker', 'Jeremy', 'Biddeford', 1999, 'Wonderboy', 2 )
insert into testing values(11, 98, 'Good', 'Wicked', 'Brunswick', 2007, 'Lefty', 2 )
insert into testing values(12, 21, 'Duffy', 'Matt', 'Biddeford', 0,null, 2 )

select Top 10 * from testing

output
1 44 Tyler Bret Brunswick 2004 Webmaster 17
2 29 House Tanner Bonny 0 NULL 5
3 25 Dee Robby Cheverus 0 NULL 4
4 7 Marshall Jeffrey Cheverus 0 NULL 3
5 27 Ramsey Travis Biddeford 0 NULL 3
6 3 Thompson Joe Brunswick 1960 Wired 3
7 5 VanDyk Josh Cheverus 0 NULL 2
8 3 Dimmen Jeff Cheverus 0 NULL 2
9 79 Danis-Pepin Simon Cheverus 0 NULL 2
10 45 Oinker Jeremy Biddeford 1999 Wonderboy 2

i tried this this is working correctly
make suure you have not any of trigger defined and getting affected of this

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 07:21:28