Select last record in a list

[MikeMV]

Hello,

I am working with a MS SQL Server 2005 database, I need to return the value of the last field in a group and I am having a problem getting it to work, the data comes from 2 different tables as follows:

StatusOptions.StatusNumber
StatusOptions.Description

StatusDetails.ID (incremented automatically)
StatusDetails.StatusNumber
StatusDetails.SalesOrderNumber

In the StatusDetails table a given sales order may have multiple entries such as received, in progress, shipped, etc. For a given sales order number I need to return the matching StatusOptions.Description field for the last entry in the StatusDetails.

For example the data may look like

StatusOptions.StatusNumber StatusOptionsDescription

1 Received
2 In-Progress
3 Shipped

StatusDetauls.ID StatusDetails.StatusNumber StatusDetails.SalesOrderNumber

1001 1 30005
1005 2 30005
1008 3 30005

I need the query to return Shipped.

I will greatly appreciate any feedback you can provide.

Mike

 

[pbaldy]

Sounds like a variant of this:

http://www.baldyweb.com/LastValue.htm

Paul
MS Access MVP 2007/2008

 

[markros]

You may try this (from the top of my head, not tested):

select * from (select StatusOptions.Description, StatusDetails.ID, StatusDetails.StatusNumber, StatusDetails.SalesOrderNumber, row_number over (partition by StatusDetails.SalesOrderNumber order by StatusDetails.StatusNumber desc) as RowNumber from StatusDetails INNER JOIN StatusOptions on StatusDetails.StatusNumber = StatusOptions.StatusNumber) AllInfo where RowNumber = 1

 

[bborissov]

What's wrong with this (or I'm missing something):

SELECT StatusDetauls.*, StatusOptions.Description
FROM StatusDetauls
INNER JOIN StatusOptions 
          ON StatusOptions.StatusNumber = StatusDetauls.StatusNumber AND
             StatusOptions.StatusNumber = 3

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thanks Borislav,

The problem is that I will never know which is the last status number, it can be anywhere from 1 - 10, since the StatusDetailsID gets incremented automatically returning the highest of this field for a given salesordernumber is the only way to get the description for the last entry.

 

[bborissov]

But you want "Shipped", isn't it?
Isn't that the LAST status for the package?
So it must be the last record in the chain, isn't it?

If you want the LAST record for SOME status then I suggest you to make a Stored Procedure and pass the status ID you want as a parameter:
(keep in mind that this code is NOT tested!!!)

CREATE PROCEDURE GetLastStatusRecord(
       @StatusId int )
AS
  BEGIN
     SELECT StatusDetauls.*,
            StatusOptions.Description
     FROM StatusDetauls
     INNER JOIN StatusOptions
          ON StatusOptions.StatusNumber = StatusDetauls.StatusNumber
     INNER JOIN (SELECT SalesOrderNumber, MAX(ID) AS Id
                        FROM StatusDetauls
                 WHERE StatusDetauls.StatusNumber = @StatusId
                 GROUP BY SalesOrderNumber) Tbl1
           ON StatusDetauls.Id = Tbl1.Id AND
              StatusDetauls.SalesOrderNumber= Tbl1.SalesOrderNumber
  END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thanks Borislav,

I'll work with your code tomorrow and let you know how it works out.

The last status for a given order may not be necessarily shipped, it could be anything, it can be in-warehouse, being picked, etc. What I need to show is what is the current status for the order.

Thanks again.

 

[markros]

Hi Borislav,

What is wrong with my suggestion, it will always get the latest status?

If you have date column in your table, then it would be even easier to get record's info with the latest date.

 

[MikeMV]

Hi Markros,

I think your code will work, I have to try it tomorrow at work.

I don't think the table has a date field, but it may be a good idea to add one.

Thanks.