Selecting rows in a record set

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

Selecting rows in a record set

Author

Topic

JFK1965
Starting Member

3 Posts

Posted - 2010-01-06 : 15:43:18

Hi all,
I'm very new to SQL Server and i'm doing a home study course to get my MSCD.
I have to do a SQL project and I'm part way through I have a query that will return an unknown number of rows but will always have a minimum od one but could be more, i've worked out how to count the number of rows but can't seem to find how to select a row to work on,for example if the query returned 3 rows how do i select row 2 to work on?

Thanks

JFK

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 15:53:47

Do you have an example?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 02:08:44

Whcih version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 02:12:50

you can use TOP 2 based on ordering by a field of your choice

JFK1965
Starting Member

3 Posts

Posted - 2010-01-07 : 04:08:36

Hi Guys thanks for ther quick replys.
Using SQL Server Expresss 2005

I have the following

SELECT Order_Details.Order_Lines.Product_ID,
Order_Details.Order_Lines.Qty,
Order_Details.Order_Lines.Qty_Despatched,
Product_Details.Products.Stockqty
FROM Order_Details.Order_Lines INNER JOIN Product_Details.Products ON
Order_Details.Order_Lines.Product_ID = Product_Details.Products_ID
WHERE Order_Details.Order.Lines_Order_No = 1234

This will always return at least one row but could be more, what I want to do is check each row to check that the qty_despatched doesn't exceed the qty ordered and then check that I have sufficient stock to despach the order. I was going to approach this by counting the number of rows create a loop that will only execute the no of times that i have rows and ech cycle of the loop move the focus to the next row, its this moving the focus to the next row that I'm unsure of how to do in SQL Server, Please help

Thanks

JFK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:14:28

do you mean this?


SELECT Order_Details.Order_Lines.Product_ID,
Order_Details.Order_Lines.Qty,
Order_Details.Order_Lines.Qty_Despatched,
Product_Details.Products.Stockqty
FROM Order_Details.Order_Lines INNER JOIN Product_Details.Products ON
Order_Details.Order_Lines.Product_ID = Product_Details.Products_ID
WHERE Order_Details.Order.Lines_Order_No = 1234
AND Order_Details.Order_Lines.Qty_Despatched<=Order_Details.Order_Lines.Qty  
AND Order_Details.Order_Lines.Qty<= Product_Details.Products.Stockqty

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-07 : 04:14:43

That should go set based and not row by row.
But to understand clear what you want we need some sample data and wanted output in relation to sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

JFK1965
Starting Member

3 Posts

Posted - 2010-01-07 : 06:51:27

Hi , some sample data as requested

Product_ID Qty Qty_Despatched StockQty
1 1 0 10
2 2 0 50
3 1 0 10

Sorry can't get this to format correctly hopefully you will be able to understand.
This is what is returned by ther Select statement what I wish to do starting at the top row is check that qty_despatched < Qty and if so then check that StockQty >= Qty if both are true then qty despatched to be changed to = Qty and StockQty to be reduced by the qty amount.

Now I can do all of the logic etc but how do I ensure that I'm on the top row to start and then once i've processed the top row retrieve the data from the second row?

I've done this before in Visual Basic coupled with an Access DB but maybe my approach has to be different for SQL Server.
One thought I've had is that I can add a field into Order_Lines table to store the row number on the order and then redo the Select statement to retrieve one row at a time, is this a better approach?

Thanks for your time and patience.

JFK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:56:11

[code]
UPDATE t
SET t.Qty_despatched=t.Qty,
t.StockQty=t.StockQty-t.Qty
FROM Table t
WHERE t.qty_despatched < t.Qty
and t.StockQty >= t.Qty
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources