1

I have the following table structure

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
4, 2, 1-1-02, proposal
5, 2, 1-2-02, cancelled
6, 3, 1-1-03, proposal
7, 3, 1-2-03, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Here is the SQL to recreate the table.

CREATE TABLE [dbo].[tableX](
    [ID] [int] NOT NULL,
    [Ordernumber] [int] NULL,
    [Date] [date] NULL,
    [Status] [nvarchar](50) NULL
) ON [PRIMARY]


INSERT INTO [tableX]
    ([ID]
    ,[Ordernumber]
    ,[Date]
    ,[Status])
VALUES
    (1, 1, '1-1-01', 'proposal'),
    (2, 1, '1-2-01', 'proposal'),
    (3, 1, '1-3-01', 'active'),
    (4, 2, '1-1-02', 'proposal'),
    (5, 2, '1-2-02', 'cancelled'),
    (6, 3, '1-1-03', 'proposal'),
    (7, 3, '1-2-03', 'proposal'),
    (8, 4, '1-1-04', 'proposal'),
    (9, 4, '1-2-04', 'active'),
    (10, 4, '1-3-04', 'active')

I need to query for all records that have a most recent state of active, in the case of the example it is ordernumber 1 and 4. Input parameter: Status = active Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
3, 1, 1-3-01, active
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active
10, 4, 1-3-04, active

Further I need to split this into history and current statuses so for state active and current the Id's 3 and 10. For state active and history the Id's 1, 2, 8 and 9. Input parameters: Status = Active, Date = Most current Result:

ID, ORDERNUMBER, DATE, STATUS
3, 1, 1-3-01, active
10, 4, 1-3-04, active

Input parameters: Status = Active, Date = Not most current Result:

ID, ORDERNUMBER, DATE, STATUS
1, 1, 1-1-01, proposal
2, 1, 1-2-01, proposal
8, 4, 1-1-04, proposal
9, 4, 1-2-04, active

I've tried a lot of possibilties, but I cannot find a query that works. Can someone help me out or point me in the right direction?

I am working in a SQL Server environment.

6
  • 1
    Do those things (1-3-04) represent dates? Eeewwwwww... Sep 5 '14 at 13:36
  • Your question is a bit fuzzy,can you edit your question with the desired results?
    – Mihai
    Sep 5 '14 at 13:37
  • In addition to the other comments (reformat dates and clarify - i.e. provide desired result and logic used to obtain that result), could you please give us CREATE TABLE blah(Col1 type...); and INSERT INTO blah VALUES (Col1...); so that those who wish to help you can do so easily!
    – Vérace
    Sep 5 '14 at 14:22
  • 2
    If you MUST, for some reason, represent dates as character strings, then you'd better come up with a representation that will order correctly. Something like '2004-01-03'. Otherwise, figuring out which one is the latest one will be the devil's own work. Sep 5 '14 at 16:08
  • @ypercube Yes, those things are dates. How would you expect them? Sep 8 '14 at 7:38
4

You can use ROW_NUMBER() and OVER clause to "group" (partition) the rows per ordernumber, then order them by date (descending) and keep only the first rows (WHERE rn = 1):

WITH cte AS
  ( SELECT  id, ordernumber, [date], status,
            rn = ROW_NUMBER() OVER (PARTITION BY ordernumber
                                    ORDER BY date DESC)
    FROM    tableX
  )
SELECT  id, ordernumber, [date], status,
FROM    cte
WHERE   rn = 1
  AND   status = 'active' ;

And I really do hope that the date column is a DATE column and not a CHAR one. Otherwise the above ORDER BY will not work as you expect.


After the last, edit, it seems that the date column is actually of DATE dataype, which is good.

But really, you should't use '1-3-04' format for input. For output it may be fine (the users of the application may like that dubious format) but for input, no. Because you can never be sure if the date that will actually be stored in the database will be 2004-01-03 or 2004-03-01 or 2001-03-04 (or some other.)

Use a format that is unambiguous, like '2004-01-03' (the ISO YYYY-MM-DD format) or even better (for SQL-Server specifically) the YYYYMMDD format: '20040103'. See the blog by Aaron Bertrand: Bad habits to kick : mis-handling date / range queries

1
  • @MartijnBurger Right, thnx, unmatched parenthesis removed. Sep 8 '14 at 8:39
1

If I understand your question correctly, this query should give you the most recent status of each order. It's crude, but it should work:

SELECT  t.ID, t.ordernumber, t.[status], t.[date] AS orderdate
FROM YourTable t
GROUP BY t.ordernumber, t.[status], t.[date], t.ID
HAVING t.[date] = (SELECT MAX([date]) FROM YourTable WHERE ordernumber = t.ordernumber)
ORDER BY [status], t.[date] 

At which point you can just filter on the status, which will give you a list of the orders with the most resent stats of 'active':

SELECT  t.ID, t.ordernumber, t.[status], t.[date] AS orderdate
FROM YourTable t
WHERE STATUS = 'Active'
GROUP BY t.ordernumber, t.[status], t.[date], t.ID
HAVING t.[date] = (SELECT MAX([date]) FROM YourTable WHERE ordernumber = t.ordernumber)
ORDER BY [status], t.[date] DESC
1
  • 1
    This is a helpful answer. However, MAX([date]) is subject to the same ordering problems pointed out in other answers. If the date really represents a date, then it needs to be represented in some datatype where MAX([date]) is really the latest date. The data in the OQ will NOT order correctly. Sep 5 '14 at 16:05
0

It think what you are after is a custom sort order? You can do this using a case statement to add an extra field, then order by it. Something like:

SELECT ID, ORDERNUMBER, DATE, STATUS, 
CASE WHEN ORDERNUMBER = 1 OR ORDERNUMBER = 4 THEN 1
WHEN STATUS =  'Active' AND /*UNKNOWN COLUMN*/ = 'Current' THEN 2
...
END as SortOrder
FROM *YourTable*
ORDER BY SortOrder

Basically it does custom sorting based on your CASE statement.

Or else you can do it using UNIONS and WHERE statements, something like:

SELECT ID, ORDERNUMBER, DATE, STATUS, 1 as SortOrder
FROM *YourTable*
WHERE ORDERNUMBER = 1 OR ORDERNUMBER = 2
UNION
SELECT ID, ORDERNUMBER, DATE, STATUS, 2
FROM *YourTable*
WHERE STATUS = 'Active' AND /*UNKNOWN COLUMN*/ = 'Current'
UNION
...
ORDER BY SortOrder

Note the ORDER BY SortOrder I believe should be on your last UNION query.

2
  • Sorry, but the idea is that the query will output the ordernumbers based on the status. You are using the ordernumbers as an input parameter if I am correct. Sep 5 '14 at 13:27
  • 1
    Ask a fuzzy question, get a fuzzy answer. It would be good to include the expected result in your question, otherwise its not entirely clear what you are asking. For example you state " ...for state active and current..." but there are no records with the "Current" state, so once can assume it is a function of the date field? It appeared to me you wanted complex customised sorting, so I gave you the option for doing that 2 ways. ORDERNUMBERS are NOT input parameters in my examples.
    – blobbles
    Sep 5 '14 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.