Home » SQL & PL/SQL » SQL & PL/SQL » Analytical Function MAX
Analytical Function MAX [message #199305] Mon, 23 October 2006 05:59 Go to next message
sambasu
Messages: 1
Registered: October 2006
Location: Nottingham, UK
Junior Member

Hi All,
I have a table called BASU_TEST_MAX like below:

SQL> desc BASU_TEST_MAX
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USER_NAME                                                      VARCHAR2(30)
 LOGON_DATE                                                     DATE
 LOGON_TIME                                                     DATE
 END_SESSION                                                    DATE


And the data looks like:

SQL> SELECT user_name,logon_date,logon_time,end_session
  2  FROM   BASU_TEST_MAX;

USER_NAME                      LOGON_DATE           LOGON_TIME           END_SESSION
------------------------------ -------------------- -------------------- --------------------
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:25 16-OCT-2006 14:31:19
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:57 16-OCT-2006 11:04:04
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:02:51 16-OCT-2006 15:35:01
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:03:14 16-OCT-2006 11:24:32
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:23:33 16-OCT-2006 14:31:16
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:31:37 17-OCT-2006 07:25:10
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 15:52:16 16-OCT-2006 15:59:18
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:11 17-OCT-2006 09:28:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:52 17-OCT-2006 08:13:01
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:52:37 17-OCT-2006 08:04:47
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:13:21 17-OCT-2006 13:13:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:23:51 17-OCT-2006 09:15:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:37:42 17-OCT-2006 09:15:05
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:01:08 17-OCT-2006 11:07:32
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:38:40 17-OCT-2006 16:04:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:43:48 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 12:41:13 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:13:52 17-OCT-2006 15:21:58
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:14:06 17-OCT-2006 15:51:43
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:22:05 17-OCT-2006 16:03:21
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:51:44 17-OCT-2006 16:03:26
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:21:27 18-OCT-2006 07:37:10
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:37:28 18-OCT-2006 08:30:13
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 11:27:35 18-OCT-2006 11:28:09
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 15:59:05 18-OCT-2006 16:03:57
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:13 19-OCT-2006 07:47:26
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:31 19-OCT-2006 15:07:29
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:55:41 19-OCT-2006 14:01:04
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 09:19:37 19-OCT-2006 14:01:03
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 10:36:59 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 11:01:21 20-OCT-2006 16:07:30
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 14:34:55 19-OCT-2006 16:57:47
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:08:05 19-OCT-2006 15:40:31
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:10:33 19-OCT-2006 16:57:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:11:42 19-OCT-2006 16:57:04
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:28:25 19-OCT-2006 16:52:56
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:01:20 20-OCT-2006 15:50:31
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:12 20-OCT-2006 09:00:30
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:49 20-OCT-2006 12:30:30
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 12:30:39 20-OCT-2006 13:22:05
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 13:22:18 20-OCT-2006 15:17:35
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:17:56 20-OCT-2006 16:10:40
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:43:51 20-OCT-2006 16:10:39
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 06:35:34 23-OCT-2006 09:15:04
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:15:14 23-OCT-2006 09:42:17
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:41:50 23-OCT-2006 10:14:14
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:42:18 23-OCT-2006 09:51:32
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:51:33 23-OCT-2006 10:20:16
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:02:20 23-OCT-2006 10:21:35
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:20:17 23-OCT-2006 10:25:44
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:25:45 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:35:53 23-OCT-2006 10:40:44

52 rows selected.

Now if I use a analytical function MAX with a order by logon_time. If you look carefully all the logon time are different and all the user name are same as "J4134". Now if I use "MAX(end_session) OVER (PARTITION BY user_name ORDER BY logon_time)" I would have expected only one value of "23/10/2006 10:41:06" whereas I am getting an output of :

SQL> SELECT user_name,logon_date,logon_time,end_session
  2       , MAX(end_session) OVER (PARTITION BY user_name ORDER BY logon_time) max_logoff
  3  FROM   BASU_TEST_MAX;

USER_NAME                      LOGON_DATE           LOGON_TIME           END_SESSION          MAX_LOGOFF
------------------------------ -------------------- -------------------- -------------------- --------------------
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:25 16-OCT-2006 14:31:19 16-OCT-2006 14:31:19
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:57 16-OCT-2006 11:04:04 16-OCT-2006 14:31:19
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:02:51 16-OCT-2006 15:35:01 16-OCT-2006 15:35:01
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:03:14 16-OCT-2006 11:24:32 16-OCT-2006 15:35:01
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:23:33 16-OCT-2006 14:31:16 16-OCT-2006 15:35:01
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:31:37 17-OCT-2006 07:25:10 17-OCT-2006 07:25:10
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 15:52:16 16-OCT-2006 15:59:18 17-OCT-2006 07:25:10
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:11 17-OCT-2006 09:28:19 17-OCT-2006 09:28:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:52 17-OCT-2006 08:13:01 17-OCT-2006 09:28:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:52:37 17-OCT-2006 08:04:47 17-OCT-2006 09:28:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:13:21 17-OCT-2006 13:13:06 17-OCT-2006 13:13:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:23:51 17-OCT-2006 09:15:06 17-OCT-2006 13:13:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:37:42 17-OCT-2006 09:15:05 17-OCT-2006 13:13:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:01:08 17-OCT-2006 11:07:32 17-OCT-2006 13:13:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:38:40 17-OCT-2006 16:04:19 17-OCT-2006 16:04:19
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:43:48 17-OCT-2006 16:04:55 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 12:41:13 17-OCT-2006 16:04:55 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:13:52 17-OCT-2006 15:21:58 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:14:06 17-OCT-2006 15:51:43 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:22:05 17-OCT-2006 16:03:21 17-OCT-2006 16:04:55
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:51:44 17-OCT-2006 16:03:26 17-OCT-2006 16:04:55
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:21:27 18-OCT-2006 07:37:10 18-OCT-2006 07:37:10
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:37:28 18-OCT-2006 08:30:13 18-OCT-2006 08:30:13
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 11:27:35 18-OCT-2006 11:28:09 18-OCT-2006 11:28:09
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 15:59:05 18-OCT-2006 16:03:57 18-OCT-2006 16:03:57
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:13 19-OCT-2006 07:47:26 19-OCT-2006 07:47:26
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:31 19-OCT-2006 15:07:29 19-OCT-2006 15:07:29
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:55:41 19-OCT-2006 14:01:04 19-OCT-2006 15:07:29
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 09:19:37 19-OCT-2006 14:01:03 19-OCT-2006 15:07:29
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 10:36:59 20-OCT-2006 16:07:32 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 11:01:21 20-OCT-2006 16:07:30 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 14:34:55 19-OCT-2006 16:57:47 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:08:05 19-OCT-2006 15:40:31 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:10:33 19-OCT-2006 16:57:06 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:11:42 19-OCT-2006 16:57:04 20-OCT-2006 16:07:32
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:28:25 19-OCT-2006 16:52:56 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:01:20 20-OCT-2006 15:50:31 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:12 20-OCT-2006 09:00:30 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:49 20-OCT-2006 12:30:30 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 12:30:39 20-OCT-2006 13:22:05 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 13:22:18 20-OCT-2006 15:17:35 20-OCT-2006 16:07:32
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:17:56 20-OCT-2006 16:10:40 20-OCT-2006 16:10:40
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:43:51 20-OCT-2006 16:10:39 20-OCT-2006 16:10:40
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 06:35:34 23-OCT-2006 09:15:04 23-OCT-2006 09:15:04
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:15:14 23-OCT-2006 09:42:17 23-OCT-2006 09:42:17
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:41:50 23-OCT-2006 10:14:14 23-OCT-2006 10:14:14
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:42:18 23-OCT-2006 09:51:32 23-OCT-2006 10:14:14
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:51:33 23-OCT-2006 10:20:16 23-OCT-2006 10:20:16
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:02:20 23-OCT-2006 10:21:35 23-OCT-2006 10:21:35
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:20:17 23-OCT-2006 10:25:44 23-OCT-2006 10:25:44
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:25:45 23-OCT-2006 10:41:06 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:35:53 23-OCT-2006 10:40:44 23-OCT-2006 10:41:06

52 rows selected.

Instead I was expecting a result set of:


USER_NAME                      LOGON_DATE           LOGON_TIME           END_SESSION          MAX_LOGOFF
------------------------------ -------------------- -------------------- -------------------- --------------------
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:25 16-OCT-2006 14:31:19 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:35:53 23-OCT-2006 10:40:44 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:02:51 16-OCT-2006 15:35:01 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 11:03:14 16-OCT-2006 11:24:32 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:23:33 16-OCT-2006 14:31:16 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 14:31:37 17-OCT-2006 07:25:10 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 15:52:16 16-OCT-2006 15:59:18 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:11 17-OCT-2006 09:28:19 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:25:52 17-OCT-2006 08:13:01 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 07:52:37 17-OCT-2006 08:04:47 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:13:21 17-OCT-2006 13:13:06 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:23:51 17-OCT-2006 09:15:06 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 08:37:42 17-OCT-2006 09:15:05 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:01:08 17-OCT-2006 11:07:32 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:38:40 17-OCT-2006 16:04:19 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 11:43:48 17-OCT-2006 16:04:55 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 12:41:13 17-OCT-2006 16:04:55 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:13:52 17-OCT-2006 15:21:58 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:14:06 17-OCT-2006 15:51:43 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:22:05 17-OCT-2006 16:03:21 23-OCT-2006 10:41:06
J4134                          17-OCT-2006 00:00:00 17-OCT-2006 15:51:44 17-OCT-2006 16:03:26 23-OCT-2006 10:41:06
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:21:27 18-OCT-2006 07:37:10 23-OCT-2006 10:41:06
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 07:37:28 18-OCT-2006 08:30:13 23-OCT-2006 10:41:06
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 11:27:35 18-OCT-2006 11:28:09 23-OCT-2006 10:41:06
J4134                          18-OCT-2006 00:00:00 18-OCT-2006 15:59:05 18-OCT-2006 16:03:57 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:13 19-OCT-2006 07:47:26 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:06:31 19-OCT-2006 15:07:29 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 07:55:41 19-OCT-2006 14:01:04 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 09:19:37 19-OCT-2006 14:01:03 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 10:36:59 20-OCT-2006 16:07:32 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 11:01:21 20-OCT-2006 16:07:30 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 14:34:55 19-OCT-2006 16:57:47 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:08:05 19-OCT-2006 15:40:31 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 15:10:33 19-OCT-2006 16:57:06 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:11:42 19-OCT-2006 16:57:04 23-OCT-2006 10:41:06
J4134                          19-OCT-2006 00:00:00 19-OCT-2006 16:28:25 19-OCT-2006 16:52:56 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:01:20 20-OCT-2006 15:50:31 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:12 20-OCT-2006 09:00:30 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 07:31:49 20-OCT-2006 12:30:30 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 12:30:39 20-OCT-2006 13:22:05 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 13:22:18 20-OCT-2006 15:17:35 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:17:56 20-OCT-2006 16:10:40 23-OCT-2006 10:41:06
J4134                          20-OCT-2006 00:00:00 20-OCT-2006 15:43:51 20-OCT-2006 16:10:39 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 06:35:34 23-OCT-2006 09:15:04 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:15:14 23-OCT-2006 09:42:17 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:41:50 23-OCT-2006 10:14:14 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:42:18 23-OCT-2006 09:51:32 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 09:51:33 23-OCT-2006 10:20:16 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:02:20 23-OCT-2006 10:21:35 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:20:17 23-OCT-2006 10:25:44 23-OCT-2006 10:41:06
J4134                          23-OCT-2006 00:00:00 23-OCT-2006 10:25:45 23-OCT-2006 10:41:06 23-OCT-2006 10:41:06
J4134                          16-OCT-2006 00:00:00 16-OCT-2006 10:48:57 16-OCT-2006 11:04:04 23-OCT-2006 10:41:06

52 rows selected.


I am getting this output only when I am removing the "ORDER BY logon_time" and make the query as below:
SELECT user_name,logon_date,logon_time,end_session
     , MAX(end_session) OVER (PARTITION BY user_name) max_logoff
FROM   BASU_TEST_MAX;


I am happy with the output. But I would like to know why is it behaving like this?

Thanks in advance.

Regards,

Samujjwal Basu
Re: Analytical Function MAX [message #199352 is a reply to message #199305] Mon, 23 October 2006 14:18 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
In this case it will evaluate the maximum as and when it goes through the group and it will also keep track of the order by clause. Since you have mentioned ordered by, it will not give you the maximum value for the entire partition instead you will get the maximum value as of that particular record ordered by logon time. If you notice your output, it will be sorted by logon time and you will find the maximum value of end_session as of that particular record. If you remove the order by clause it will apply the max() function on the entire partition.

cheers
Previous Topic: Using Count in a group function
Next Topic: Query Issues
Goto Forum:
  


Current Time: Wed Dec 07 18:28:57 CST 2016

Total time taken to generate the page: 0.12961 seconds