CODEWITHSUNDEEP
sql
Will.D
Will.D

Reputation: 83

SQL : Return the records that are the oldest in date

So.....

I have a table (WOSTATUS). With the columns (WONUM, STATUS, CHANGEDATE).

Now there are multiple statuses to a WONUM. The one I am interested in is status ACTION. However the WONUM can be set to ACTION multiple times in its life, and in this table each time it is set to ACTION it becomes a new row.

What I want is a list of the WONUM when it was first put to ACTION. The date is recorded in CHANGEDATE.

Please help.

Upvotes: 2

Views: 45

Answers (2)

Eric Pitz
Eric Pitz

Reputation: 364

Do I understand it correctly that each time the status changes, you add a new row to the table. And you want the WONUM of the entries when the first status was 'ACTION' ?

In that case try the following:

SELECT WONUM
FROM WOSTATUS
WHERE (SELECT TOP 1 STATUS
       FROM WOSTATUS T
       WHERE T.WONUM = WONUM
       ORDER BY CHANGEDATE) = 'ACTION'

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can use the following GROUP BY:

SELECT WONUM, MIN(CHANGEDATE) 
FROM WOSTATUS 
WHERE STATUS = 'ACTION' 
GROUP BY WONUM, STATUS

Upvotes: 1

Related Questions