I have a view listing tickets and reports for those tickets. I want to query LAST REPORT's OPERATOR
SELECT OPERATOR AS EXPR2, NUMBERPRGN, IS_BITIS AS EXPR1
FROM SCADMIN.V_ESKALASYON_2
WHERE (NUMBERPRGN = 'IM1289657')
ORDER BY NUMBERPRGN, IS_BITIS DESC
That query brings the resultset
| IM1289657 | OGUZY | 04.12.2006 14:01:09 |
| IM1289657 | MUJDEO | 01.12.2006 10:42:30 |
| IM1289657 | MUJDEO | 28.11.2006 10:58:22 |
| IM1289657 | ILKERD | 20.11.2006 14:36:12 |
| IM1289657 | ILKERD | 13.11.2006 16:02:27 |
| IM1289657 | ILKERD | 07.11.2006 14:02:21 |
| IM1289657 | ILKERD | 31.10.2006 15:47:56 |
| IM1289657 | SINANK | 19.10.2006 13:00:00 |
| IM1289657 | OGUZY | 18.10.2006 17:25:56 |
| | |
Can you help to recover the query sentence above to return only the red marked record (LAST REPORT info written)
Thanks :)
SELECT TOP (1)...|||
Hoops, I have forgotten to say that I use that sentence to query from Oracle (SSIS). And there are lots of ticket numbers. I want to query only the red bold ones from Oracle (the criteria of red bold records is that they are last report for the ticket)
Could you help me?
| IM1124672 | ARIFOZ | 16.11.2006 13:16 |
| IM1124672 | ARIFOZ | 16.11.2006 13:16 |
| IM1124672 | ARIFOZ | 26.10.2006 10:11 |
| IM1124672 | ARIFOZ | 28.09.2006 11:30 |
| IM1124672 | ARIFOZ | 08.09.2006 13:33 |
| IM1124672 | ARIFOZ | 17.08.2006 14:18 |
| IM1124672 | ARIFOZ | 02.08.2006 11:34 |
| IM1124672 | ARIFOZ | 18.07.2006 08:20 |
| IM1124672 | ARIFOZ | 04.07.2006 10:02 |
| IM1124672 | ARIFOZ | 26.06.2006 11:25 |
| IM1241042 | ILKERD | 28.11.2006 11:17 |
| IM1241042 | AYHANK | 24.11.2006 10:00 |
| IM1241042 | ILKERD | 20.11.2006 11:53 |
| IM1241042 | AYHANK | 17.11.2006 12:10 |
| IM1241042 | ILKERD | 13.11.2006 15:52 |
| IM1241042 | ILKERD | 07.11.2006 13:42 |
| IM1241042 | ILKERD | 30.10.2006 15:23 |
| IM1241042 | ILKERD | 12.10.2006 11:19 |
| IM1241042 | ILKERD | 05.10.2006 11:25 |
| IM1241042 | ILKERD | 28.09.2006 14:47 |
| IM1241042 | ILKERD | 22.09.2006 15:22 |
| IM1241042 | ILKERD | 12.09.2006 14:30 |
| IM1241042 | ILKERD | 07.09.2006 16:28 |
| IM1241042 | AYHANK | 06.09.2006 12:10 |
| IM1241042 | AYHANK | 04.09.2006 11:25 |
| IM1251338 | ILKERD | 28.11.2006 11:22 |
| IM1251338 | ILKERD | 20.11.2006 12:15 |
| IM1251338 | MUJDEO | 15.11.2006 12:36 |
| IM1251338 | MUJDEO | 09.11.2006 15:34 |
| IM1251338 | ILKERD | 07.11.2006 13:44 |
| IM1251338 | ILKERD | 30.10.2006 15:28 |
| IM1251338 | ILKERD | 12.10.2006 11:23 |
| IM1251338 | ILKERD | 05.10.2006 11:40 |
| IM1251338 | ILKERD | 28.09.2006 14:57 |
| IM1251338 | ILKERD | 22.09.2006 15:48 |
| IM1251338 | ALPS | 15.09.2006 19:20 |
| IM1251338 | ALPS | 15.09.2006 17:50 |
| IM1251338 | ILHANA | 14.09.2006 16:24 |
| IM1251338 | HAKANM | 14.09.2006 13:23 |
| IM1253690 | ILKERD | 28.11.2006 11:26 |
| IM1253690 | ILKERD | 13.11.2006 15:54 |
| IM1253690 | ILKERD | 07.11.2006 13:45 |
| IM1253690 | ILKERD | 30.10.2006 15:30 |
| IM1253690 | ILKERD | 12.10.2006 11:46 |
| IM1253690 | ILKERD | 05.10.2006 13:38 |
| IM1253690 | FERHATY | 25.09.2006 15:30 |
| IM1253690 | ERCAND | 23.09.2006 12:00 |
| IM1253690 | FERHATY | 18.09.2006 15:00 |
| IM1267973 | ILKERD | 28.11.2006 11:27 |
| IM1267973 | ILKERD | 20.11.2006 14:11 |
| IM1267973 | ILKERD | 13.11.2006 15:57 |
| IM1267973 | ILKERD | 07.11.2006 13:48 |
| IM1267973 | ILKERD | 30.10.2006 15:34 |
| IM1267973 | ILKERD | 12.10.2006 12:26 |
| IM1267973 | ILKERD | 05.10.2006 14:34 |
|||
I don't know Oracle very well... I hope that this works...
SELECT NUMBERPRGN, OPERATOR, IS_BITIS
FROM SCADMIN.V_ESKALASYON_2 AS Main INNER JOIN
(SELECT NUMBERPRGN, MAX(IS_BITIS) AS Date
FROM SCADMIN.V_ESKALASYON_2
GROUP BY NUMBERPRGN) AS Sub ON Main.NUMBERPRGN = Sub.NUMBERPRGN AND Main.IS_BITIS = Sub.Date
ORDER BY NUMBERPRGN
|||Thanks Lepaca, SQL is SQL (wherever queried). You know PL/SQL also, I think ;-)
No comments:
Post a Comment