SELECT TOP 1 is not returning the right number

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 1 is not returning the right number

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-13 : 23:14:05

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

982 Posts

Posted - 2013-05-14 : 00:12:48

hi, try this.


SELECT	TOP 1 ProductModelID
FROM Production.ProductModel
ORDER BY ProductModelID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:17:52

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-14 : 00:33:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:49:35

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:52:12

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-14 : 01:43:17

thank visakh =)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 01:47:54

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-14 : 14:47:27

quote:
Originally posted by waterduck

hi, try this.

SELECT TOP 1 ProductModelID
FROM Production.ProductModel
ORDER BY ProductModelID

It worked. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources