SELECT TOP 1 is not working - SQL Server Forums

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 working

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-18 : 17:39:26

I am using the AdventureWorks2012 database for SQL Server Express on my PC. I am using the table Production.WorkOrder.

I am processing 5 rows one at a time and displaying the value for each row in the column called ProductID. The first 5 values listed are: 722, 725, 726, 729, 730. However, my output is: 3, 316, 324, 327, 328.

How do I retrieve the top 5 values in the 700's? It appears as though SQL is implicitly sorting and taking the top 5 values (which appear to be defined as the 5 smallest values). I tried this with and w/o ORDER BY so I don't know what else to try.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-18 : 19:15:13

Try this:
[CODE]
SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and
ProductID < 800 order by ProductID DESC;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:12:14

it should be ProductID ASC as OP wants values in ascending order

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

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-20 : 16:23:06

quote:
Originally posted by MuMu88

Try this:
[CODE]
SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and
ProductID < 800 order by ProductID DESC;

[/CODE]

I was hoping to grab the top 5 rows regardless of their value. In another example, the numbers might not be in the 700s, so I couldn't rely on hardcoding > 700. Is there a way to just extract the top 5 values no matter what they are?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-21 : 00:40:11

-- This is for retrieving top 5 rows randomly
SELECT TOP 5 *
FROM Production.WorkOrder
ORDER BY NEWID()

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 04:54:07

quote:
Originally posted by Rock_query

quote:
Originally posted by MuMu88

Try this:
[CODE]
SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and
ProductID < 800 order by ProductID DESC;

[/CODE]

I was hoping to grab the top 5 rows regardless of their value. In another example, the numbers might not be in the 700s, so I couldn't rely on hardcoding > 700. Is there a way to just extract the top 5 values no matter what they are?

Ok. But is there a need for numbers to be in same range? is all in 700s,600s etc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources