Error in top (100) percent

Question

ALTER VIEW [dbo].[View_GpsList]
AS
SELECT     TOP (100) PERCENT	dbo.tbl_unit.un_id, 
								dbo.tbl_unit.un_imei, 
								dbo.tbl_unit.un_phoneN, 
								dbo.tbl_unit.un_pwd, 
								dbo.tbl_unit.t_id, 
								dbo.tbl_unit.un_actid, 
								dbo.tbl_unit.un_Driver, 
								dbo.tbl_unit.un_lastlat, 
								dbo.tbl_unit.un_lastlon, 
								dbo.tbl_unit.un_lastTime, 
								dbo.tbl_driver.dr_id, 
								dbo.tbl_driver.dr_fname + N' ' + dbo.tbl_driver.dr_lname AS dr_fname, 
								dbo.tbl_driver.dr_phone, 
								dbo.tbl_driver.dr_personel, 
								ROUND(dbo.tbl_unit.un_lastSpeed, 0) AS Speed, 
								dbo.tbl_uu.un_alias, 
								dbo.tbl_driver.dr_CarNo AS un_CarNo, 
								dbo.tbl_driver.dr_man AS un_man, 
								dbo.tbl_driver.dr_nah AS un_nah, 
								dbo.tbl_MissionType.[Type_Name] AS MissionType

FROM							dbo.tbl_unit 
									INNER JOIN
								dbo.tbl_driver		ON 
									dbo.tbl_unit.un_Driver = dbo.tbl_driver.dr_id 
									INNER JOIN
								dbo.tbl_uu			ON 
									dbo.tbl_unit.un_id = dbo.tbl_uu.un_id 
									LEFT OUTER JOIN
								dbo.tbl_MissionType ON 
									dbo.tbl_unit.un_lastDigitalInputs = dbo.tbl_MissionType.Type_Code
									
WHERE		(dbo.tbl_driver.dr_id >= 336)
ORDER BY	dbo.tbl_unit.un_lastTime DESC

HI there

i have some error in this view ! but i think everything is ok !!! Help me please.

sql's version is 2005.

Best regard

Answer 1

ALTER VIEW [dbo].[View_GpsList]
AS
SELECT     TOP (100) PERCENT	dbo.tbl_unit.un_id, 
								dbo.tbl_unit.un_imei, 
								dbo.tbl_unit.un_phoneN, 
								dbo.tbl_unit.un_pwd, 
								dbo.tbl_unit.t_id, 
								dbo.tbl_unit.un_actid, 
								dbo.tbl_unit.un_Driver, 
								dbo.tbl_unit.un_lastlat, 
								dbo.tbl_unit.un_lastlon, 
								dbo.tbl_unit.un_lastTime, 
								dbo.tbl_driver.dr_id, 
								dbo.tbl_driver.dr_fname + N' ' + dbo.tbl_driver.dr_lname AS dr_fname, 
								dbo.tbl_driver.dr_phone, 
								dbo.tbl_driver.dr_personel, 
								ROUND(dbo.tbl_unit.un_lastSpeed, 0) AS Speed, 
								dbo.tbl_uu.un_alias, 
								dbo.tbl_driver.dr_CarNo AS un_CarNo, 
								dbo.tbl_driver.dr_man AS un_man, 
								dbo.tbl_driver.dr_nah AS un_nah, 
								dbo.tbl_MissionType.[Type_Name] AS MissionType

FROM							dbo.tbl_unit 
									INNER JOIN
								dbo.tbl_driver		ON 
									dbo.tbl_unit.un_Driver = dbo.tbl_driver.dr_id 
									INNER JOIN
								dbo.tbl_uu			ON 
									dbo.tbl_unit.un_id = dbo.tbl_uu.un_id 
									LEFT OUTER JOIN
								dbo.tbl_MissionType ON 
									dbo.tbl_unit.un_lastDigitalInputs = dbo.tbl_MissionType.Type_Code
									
WHERE		(dbo.tbl_driver.dr_id >= 336)
ORDER BY	dbo.tbl_unit.un_lastTime DESC

HI there

i have some error in this view ! but i think everything is ok !!! Help me please.

sql's version is 2005.

Best regard

May sql server 2005 don't support top (100) percent ?!!!

Answer 2

I dont know why but when i delete () from (100)  view executed :) 

My problem solved

Answer 3

I dont know why but when i delete () from (100)  view executed :) 

I see you have answered your own question but be aware that ORDER BY in a view does not guarantee ordering; you should specify ORDER BY when selecting from the view.  ORDER BY in a view is only used in conjunction with TOP to limit the rows returned so it servers no purpose with 100 percent.  See http://msdn.microsoft.com/en-us/library/ms188385(v=sql.90).aspx.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Thanks Dan  , I know about not guarantee but i need that ORDER BY and i cant delete that ( order from master :D ) 

Again thanks for guide.