0

Hello,

I was looking for some help with the below query. I need to only return the first 'Carton.CartonNumber'. I tried adding something like ( Select Min(Carton.CartonNumber) as FirstCarton from Cartons group by Carton.ID) dispite much Googling but cant get it right because of all the other joins confusing me.

Please could you assist? Thank you

SELECT OrderLine.LineNo, Order.OrderNumber, Order.Description, WarehouseOrder.WhsRef, 
       WhsPick.PickRequired, WhsPick.PickAvailable, WhsPick.EstimatedDispatch, Carton.CartonNumber
  FROM WhsPick 
       INNER JOIN WarehouseOrder ON WhsPick.ID = WarehouseOrder.PickID 
       INNER JOIN OrderLine 
       INNER JOIN Order ON OrderLine.OrderID = Order.ID 
                        ON WarehouseOrder.ID = Order.WarehouseOrderID 
       INNER JOIN Carton ON WhsPick.ID = Carton.PickID

Edited by Reverend Jim: Corrected code formatting

7
Contributors
9
Replies
36
Views
1 Year
Discussion Span
Last Post by dinesh.isuranga
0

First isnt a sql function for SQL Server.

Edited by tig2810

0

Instead of bashing what doesn't work, it would be better if you create a SqlFiddle with some data, so we can actually see what's going on and what you want to extract. SELECT TOP 1 *definitely works in SQL server, although your query might need some tweaking.

0

Hello,

In mysql you can use LIMIT to limit the number of rows of output:

    SELECT OrderLine.LineNo, Order.OrderNumber, Order.Description, WarehouseOrder.WhsRef, 
           WhsPick.PickRequired, WhsPick.PickAvailable, WhsPick.EstimatedDispatch, Carton.CartonNumber
      FROM WhsPick 
           INNER JOIN WarehouseOrder ON WhsPick.ID = WarehouseOrder.PickID 
           INNER JOIN OrderLine 
           INNER JOIN Order ON OrderLine.OrderID = Order.ID 
                            ON WarehouseOrder.ID = Order.WarehouseOrderID 
           INNER JOIN Carton ON WhsPick.ID = Carton.PickID
           LIMIT 1

limit uses the following parameters:

SELECT ... FROM ... ORDER BY ... LIMIT 0, 20; retrieve first 20 rows
SELECT ... FROM ... ORDER BY ... LIMIT 20, 20; skip 20 rows, retrieve next 20
SELECT ... FROM ... ORDER BY ... LIMIT 40, 20; skip 40 rows, retrieve next 20
etc.
-2

According to my knowldge Select is not a sql function.................................

0

In SQL 2012 there is a new clause available OFFSET-FETCH. But the trick is first you have to order the result. Please add below clause after ordering.
OFFSET 0 ROW FETCH 1 ROW ONLY;

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.