SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT TOP 1 is not returning the right number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/13/2013 :  23:14:05  Show Profile  Reply with Quote
Here is my code:

SELECT TOP 1 ProductModelID
FROM Production.ProductModel

The values in the column ProductModelID range from 1 to 128. But the value that is being returned is 104, instead of 1.

If I try TOP 5, I get 104, 17, 12, 14, and 120, instead of 1-5.

I am using SQL Server Express and the database is AdventureWorks2012.

Why are the wrong values being returned?

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/14/2013 :  00:12:48  Show Profile  Reply with Quote
hi, try this.

SELECT	TOP 1 ProductModelID
FROM Production.ProductModel
ORDER BY ProductModelID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/14/2013 :  00:17:52  Show Profile  Reply with Quote
You're not telling it to sequence on any specific order. See Waterducks suggestion it has an explicit ORDER BY specifying the order in which you want results. Please keep in mind that there's no concept of order in sql table unless you specify it by means of an ORDER BY. So make sure you use an explicit ORDER BY to get TOP n records based on your requirement.
Though not relevant in above case, also keep in mind that in case of ties (same values repeating) and you want all of them to be returned you can use the below option


SELECT	TOP 1 WITH TIES  ProductModelID
FROM Production.ProductModel
ORDER BY ProductModelID



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/14/2013 :  00:33:51  Show Profile  Reply with Quote
hi visakh, sorry to bother, need yours help to explain further (x) with ties works

SELECT TOP 74954 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty

return 74954 rows
vs
SELECT TOP 74955 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty

return 89154 rows

Edited by - waterduck on 05/14/2013 00:34:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/14/2013 :  00:49:35  Show Profile  Reply with Quote
quote:
Originally posted by waterduck

hi visakh, sorry to bother, need yours help to explain further (x) with ties works

SELECT TOP 74954 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty

return 74954 rows
vs
SELECT TOP 74955 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty

return 89154 rows



Thats because you had 14200 records in your table with same value of OrderQty fields as the 74955 th record. So WITH TIES will bring all of them causing the row count to raise to 89154

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/14/2013 :  00:52:12  Show Profile  Reply with Quote
To check that do this

DECLARE @74955thValue Numeric(10,2)

SELECT TOP 1 @74955thValue = OrderQty
FROM (SELECT TOP 74955 OrderQty
      FROM Sales.SalesOrderDetail
      ORDER BY OrderQty
      )t
ORDER BY OrderQty DESC

SELECT COUNT(*) 
FROM Sales.SalesOrderDetail
WHERE OrderQty = @74955thValue 




you should get 14200 as the value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/14/2013 :  01:43:17  Show Profile  Reply with Quote
thank visakh =)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/14/2013 :  01:47:54  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/14/2013 :  14:47:27  Show Profile  Reply with Quote
quote:
Originally posted by waterduck

hi, try this.

SELECT	TOP 1 ProductModelID
FROM Production.ProductModel
ORDER BY ProductModelID




It worked. Thank you very much.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000