correlated query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

correlated query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-10 : 08:16:14

Ajaya writes "SERIAL DATE STATUS

123 10/02/06 PENDING
123 01/06/06 DECLINE >

345 08/30/06 APPROVE>
345 10/05/06 APPROVE

567 08/14/06 APPROVE>
567 08/22/06 APPROVE

678 10/17/06 OTHER STATUS
678 10/17/06 APPROVE>
678 10/24/06 APPROVE

789 10/04/06 DECLINE
789 10/06/06 OTHER STATUS
789 10/06/06 APPROVE>

I am looking for a sql which extract
EARLIEST DATE IF STATUS = APPROVE OR DECLINE, ONLY NEED TO CONSIDER THESE TWO STATUSES.

for example serial = 789, I want to select the last row, since it is approved status.

The arrow in the right side , means i want to select that row from my sql.
If both statuses(decline and approve) is falls for a serial number, then I want to select the row with approved status with earliest date. example sir = 789

Please help"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 08:41:33

select mt.*
from mytable mt
inner join (select serial, min(date) y from mytable where status = 'approve') q on q.serial = mt.serial and q.y = mt.date

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources