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 working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 05/18/2013 :  17:39:26  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/18/2013 :  19:15:13  Show Profile  Reply with Quote
Try this:

SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and 
                    ProductID < 800 order by ProductID DESC;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/20/2013 :  01:12:14  Show Profile  Reply with Quote
it should be ProductID ASC as OP wants values in ascending order

------------------------------------------------------------------------------------------------------
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/20/2013 :  16:23:06  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Try this:

SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and 
                    ProductID < 800 order by ProductID DESC;





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?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 05/21/2013 :  00:40:11  Show Profile  Reply with Quote
-- This is for retrieving top 5 rows randomly
SELECT TOP 5 *
FROM Production.WorkOrder
ORDER BY NEWID()

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  04:54:07  Show Profile  Reply with Quote
quote:
Originally posted by Rock_query

quote:
Originally posted by MuMu88

Try this:

SELECT TOP 5 * from Production.WorkOrder where ProductID > 700 and 
                    ProductID < 800 order by ProductID DESC;





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
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