SQL query with one join only returning the first record

[tig2810]

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

[Reverend Jim]

Try SELECT FIRST...

[tig2810]

First isnt a sql function for SQL Server.

[Reverend Jim]

Sorry. That should have been SELECT TOP(1)....

[KelvinSmith]

Can you please specify which output have you expected?

[pritaeas]

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.

[rch1231]

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.

[Ammar_7]

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

[Reverend Jim]

According to my knowldge Select is not a sql function

Seriously?

[dinesh.isuranga]

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;