Select Rows With Max Value

We often need to select rows based on the maximum value of a specific column, such as date. Let’s say we  have a table that stores customers orders, and we want to list the last order dates and amounts for each customer.

Here is a demo data set and query to achieve this. The same query can be extended to select rows based on the maximum value of any other column.

The table data set is:

SQL> select * from t_orders
  2  order by cust_id, order_date desc;

CUST_ID         ORDER_DAT  ORDER_AMT
--------------- --------- ----------
100             27-APR-10          8
100             17-JAN-10         21
101             02-AUG-10          7
101             26-JUL-10          4
101             17-APR-10          4
101             28-MAR-10         55
102             28-MAR-10          9
103             27-APR-10          4
103             18-MAR-10          3
103             17-JAN-10         15
103             07-JAN-10          1
104             26-JUN-10          2
104             18-MAR-10          2

13 rows selected.

We see from the table t_orders that four customers have placed a total of 13 orders. Of these we want to choose the latest order details for each customer.

The query to do this is:

SQL> -- Last order date of each customer
SQL> select cust_id
  2       , last_order_date
  3       , order_amt last_order_amt
  4  from
  5  (
  6    select cust_id
  7       , order_date
  8       , max(order_date) over
  9           (partition by cust_id) last_order_date
 10       , order_amt
 11    from t_orders
 12  )
 13  where order_date = last_order_date;

CUST_ID         LAST_ORDE LAST_ORDER_AMT
--------------- --------- --------------
100             27-APR-10              8
101             02-AUG-10              7
102             28-MAR-10              9
103             27-APR-10              4
104             26-JUN-10              2

The inner query uses the analytics function max() on a partition by customer id. The outer query picks that record for each customer in which the order date matches the maximum order date.

A similar query to select order details for the maximum order amount placed by each customer. In this case, max() is applied to order_amt.

SQL> -- Max order amount of each customer
SQL> select cust_id
  2       , order_date date_of_max_order
  3       , max_order_amt
  4  from
  5  (
  6    select cust_id
  7       , order_date
  8       , order_amt
  9       , max(order_amt) over
 10           (partition by cust_id) max_order_amt
 11    from t_orders
 12  )
 13  where order_amt = max_order_amt;

CUST_ID         DATE_OF_M MAX_ORDER_AMT
--------------- --------- -------------
100             17-JAN-10            21
101             28-MAR-10            55
102             28-MAR-10             9
103             17-JAN-10            15
104             26-JUN-10             2
104             18-MAR-10             2

Notice that in the second query, two records are retrieved for cust_id 104 as it has two orders with the same maximum amount.

4 Ways to Join Only The First Row in SQL

For today’s daily report, we need a list of users and the most recent widget each user has created. We have a users table and a widgets table, and each user has many widgets. users.id is the primary key on users, and widgets.user_id is the corresponding foreign key in widgets.

To solve this problem, we need to join only the first row. There are several ways to do this. Here are a few different techniques and when to use them.

Use Correlated Subqueries when the foreign key is indexed

Correlated subqueries are subqueries that depend on the outer query. It’s like a for loop in SQL. The subquery will run once for each row in the outer query:

select * from users join widgets on widgets.id = (
    select id from widgets
    where widgets.user_id = users.id
    order by created_at desc
    limit 1
)

Notice the where widgets.user_id = users.id clause in the subquery. It queries the widgets table once for each user row and selects that user’s most recentwidget row. It’s very efficient if user_id is indexed and there are few users.

Use a Complete Subquery when you don’t have indexes

Correlated subqueries break down when the foreign key isn’t indexed, because each subquery will require a full table scan.

In that case, we can speed things up by rewriting the query to use a single subquery, only scanning the widgets table once:

select * from users join (
    select distinct on (user_id) * from widgets
    order by user_id, created_at desc
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id

This new subquery returns a list of the most recent widgets, one for each user. We then join it to the users table to get our list.

We’ve used Postgres’ DISTINCT ON syntax to easily query for only one widget peruser_id. If your database doesn’t support something like DISTINCT ON, you have two options:

Use Nested Subqueries if you have an ordered ID column

In our example, the most recent row always has the highest id value. This means that even without DISTINCT ON, we can cheat with our nested subqueries like this:

select * from users join (
    select * from widgets
    where id in (
        select max(id) from widgets group by user_id
    )
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id

We start by selecting the list of IDs repreenting the most recent widget per user. Then we filter the main widgets table to those IDs. This gets us the same result as DISTINCT ON since sorting by id and created_at happen to be equivalent.

Use Window Functions if you need more control

If your table doesn’t have an id column, or you can’t depend on its min or max to be the most recent row, use row_number with a window function. It’s a little more complicated, but a lot more flexible:

select * from users join (
    select * from (
        select *, row_number() over (
            partition by user_id
            order by created_at desc
        ) as row_num
        from widgets
    ) as ordered_widgets
    where ordered_widgets.row_num = 1
) as most_recent_user_widget
on users.id = most_recent_user_widget.user_id
order by users.id

The interesting part is here:

select *, row_number() over (
    partition by user_id
    order by created_at desc
) as row_num
from widgets

over (partition by user_id order by created_at desc specifies a sub-table, called a window, per user_id, and sorts those windows by created_at desc.row_number() returns a row’s position within its window. Thus the first widget for each user_id will have row_number 1.

In the outer subquery, we select only the rows with a row_number of 1. With a similar query, you could get the 2nd or 3rd or 10th rows instead.

In a future post we’ll go deeper on window functions and how they can make queries like this one even more powerful!

How to share Wi-Fi in Windows 8 with Internet connection sharing (ICS)

This step-by- guide explains how to share your internet with Internet Connection Sharing (ICS) and turn a Windows 8 computer into a Wi-Fi hotspot.

Whenever I want to share an Internet connection through Wi-Fi and turn my Windows machine in Wi-FI access point, I find myself googling again because I somehow can’t memorize the procedure. Google is a great brain extension for people with a bad memory like me. However, in this case, it always leads to frustration because many guides about Internet Connection Sharing (ICS) are incomplete, describe the procedure in the wrong order, or confuse different Windows versions. I wonder if all those how-to bloggers out there just copy from one another or if anyone actually tried their Wi-Fi sharing step-by-step guide.

With Windows 8, things got worse. The forums are full of posts of frustrated users who just can’t connect to their Windows 8 Access Point. This is probably due to the fact that things are a bit different than in Windows 7, and many how-to bloggers just re-posted their old articles with a new heading. (I didn’t verify this and, as mentioned above, I only remember roughly how it worked in Windows 7.)

Anyway, next time I can just search on 4sysops and I will find an ICS guide for Windows 8 that works.

1. Launch a command prompt with administrator privileges. (Type cmd on the Start Screen, right-click the icon, and click Run as Administrator.)
2. Type netsh wlan show drivers and verify if Hosted network supported says Yes. If it says No, you are done. Your NIC doesn’t support Wi-Fi sharing.
   
3. Type netsh wlan set hostednetwork mode=allow ssid=Hotspot key=12345678. (Note that you can replace “Hotspot” and the security key with your favorite settings.)
4. Type netsh wlan start hostednetwork.
   
5. Open the Network and Sharing Center in the Control Panel. (Type Control Panel on the Start Screen and then Network in the Control Panel search box.) Then, clickChange adapter settings.
   
6. Now comes the part that appears to be different in Windows 8 than in previous Windows versions. You will notice that a new network adapter appeared, calledLocal Area Network Connection, followed by a number that can be different from computer to computer. The device name is Microsoft Hosted Virtual Network Adapter. The ssid you used above (Hotspot in this guide) will appear under the name. If you see Identifying under its icon or just Enabled, be patient and wait untilHotspot (the ssid is used in step 3) appears.
   
   
7. Right-click the network adapter that is connected to the Internet and selectProperties. (This can be an Ethernet adapter, a mobile network adapter, or your phone that is connected through USB. In this guide, it is my Samsung phone.)
   
8. Click the Sharing tab and then enable Allow other network users to connect through this computer’s Internet connection.
9. In Windows 7, if I remember it right (correct me if I’m wrong), you would have selected the Wi-Fi adapter under Home Networking Connection. If you do this in Windows 8, you will be able to establish a Wi-Fi connection to your Windows 8 Access Point, but your client won’t receive an IP address through the Windows 8 DHCP server. Thus you have to select the Local Area Network Connection adapter I mentioned in step 6 as your Home Networking Connection.
   

Data Guard Queries

 
 
Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH      CLOSING               1      69479     932864        261

ARCH      CLOSING               1      69480     928768        670

ARCH      CLOSING               2      75336     933888        654

ARCH      CLOSING               2      78079     930816        842

ARCH      CLOSING               1      69475     943104         79

RFS       IDLE                  0          0          0          0

...

RFS       RECEIVING             1      69481     688130       1024

MRP0      WAIT_FOR_LOG          2      78080          0          0

RFS       IDLE                  2      78080     873759          3

• Last applied log: Run this query on the standby database to see the last applied archivelog sequence number for each thread.

SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

         1          69479

         2          78079

• Archivelog difference: Run this on primary database. (not for real time apply)

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF

---------- ---------- ----------- -------------------- ----------

         2      78083       78082 01-JUL-2013 16:05:25          1

         1      69486       69485 01-JUL-2013 16:08:21          1

• Apply/transport lags: v$dataguard_stats view will show the general synchronization status of standby database. Better to use on 11gR2 even with the latest PSU (Check bugs : 13394040, 7119382, 9968073, 7507011, 13045332, 6874522).

SQL> set lines 200

SQL> col name format a40

SQL> col value format a20

SQL> select * from v$dataguard_stats;

NAME                     VALUE             UNIT        TIME_COMPUTED         DATUM_TIME

------------------------ ----------------- ------      --------------------- ---------------------

transport lag            +00 00:09:44      …           07/01/2013 15:49:29   07/01/2013 15:49:27

apply lag                +00 00:09:44      …           07/01/2013 15:49:29   07/01/2013 15:49:27

apply finish time        +00 00:00:00.001  …           07/01/2013 15:49:29

estimated startup time   27                second      07/01/2013 15:49:29   

• Apply rate: To find out the speed of media recovery in a standby database, you can use this query:

SQL> set lines 200

SQL> col type format a30

SQL> col ITEM format a20

SQL> col comments format a20

SQL> select * from v$recovery_progress;

START_TIM TYPE             ITEM                 UNITS        SOFAR      TOTAL TIMESTAMP COMMENTS

--------- ---------------- -------------------- ------------------ ---------- --------- ----

20-JUN-13 Media Recovery   Log Files            Files         3363          0

20-JUN-13 Media Recovery   Active Apply Rate    KB/sec       21584          0

20-JUN-13 Media Recovery   Average Apply Rate   KB/sec        3239          0

20-JUN-13 Media Recovery   Maximum Apply Rate   KB/sec       48913          0

20-JUN-13 Media Recovery   Redo Applied         Megabytes  2953165          0

20-JUN-13 Media Recovery   Last Applied Redo    SCN+Time         0          0 01-JUL-13

20-JUN-13 Media Recovery   Active Time          Seconds     233822          0

20-JUN-13 Media Recovery   Apply Time per Log   Seconds         57          0

20-JUN-13 Media Recovery   Checkpoint Time per  Seconds         11          0

                           Log

20-JUN-13 Media Recovery   Elapsed Time         Seconds     933565          0

20-JUN-13 Media Recovery   Standby Apply Lag    Seconds        483          0

11 rows selected.

You can also use below before 11gR2. (Deprecated in 11gR2):

SQL> select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;

• To check Redo apply mode on physical standby database:

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;

RECOVERY_MODE

-----------------------

MANAGED

• To check what MRP process is waiting:

select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'))

EVENT                                           WAIT_TIME SECONDS_IN_WAIT

---------------------------------------------- ---------- ---------------

parallel recovery control message reply                 0               0

• Archive Lag Histogram: The  V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds. For example following output shows that in 07/01/2013 archive lag reached 5 hours and in 06/15/2013 gap was 22 hours which was resolved after more than a week.

SQL> col name format a10

SQL> select * from  V$STANDBY_EVENT_HISTOGRAM;

NAME             TIME UNIT             COUNT LAST_TIME_UPDATED

---------- ---------- ------------  -------- --------------------

apply lag           0 seconds              0

apply lag           1 seconds              1 04/13/2013 01:40:23

apply lag           2 seconds              1 04/13/2013 01:40:24

apply lag           3 seconds              1 04/13/2013 01:40:25

apply lag           4 seconds              1 04/13/2013 01:40:26

...

apply lag          25 seconds              3 05/21/2013 06:31:19

apply lag          26 seconds              3 05/21/2013 06:31:20

apply lag          27 seconds              3 05/21/2013 06:31:23

apply lag          28 seconds              5 05/21/2013 06:31:22

apply lag          29 seconds              1 05/15/2013 07:47:46

apply lag          30 seconds              4 05/21/2013 06:31:24

...

apply lag          44 seconds              8 06/26/2013 00:33:14

apply lag          45 seconds              8 06/26/2013 00:33:15

apply lag          46 seconds              8 06/26/2013 00:33:17

apply lag          47 seconds              8 06/26/2013 00:33:18

apply lag          48 seconds              9 06/26/2013 00:33:19

...

apply lag          57 seconds             29 06/26/2013 06:33:02

apply lag          58 seconds             25 06/26/2013 06:33:27

apply lag          59 seconds             28 06/26/2013 06:33:28

apply lag           1 minutes              0

apply lag           2 minutes           9316 06/30/2013 18:33:45

apply lag           3 minutes          94601 07/01/2013 14:23:11

apply lag           4 minutes         209262 07/01/2013 14:56:13

apply lag           5 minutes         355744 07/01/2013 16:02:33

apply lag           6 minutes         522176 07/01/2013 16:03:30

apply lag           7 minutes         634199 07/01/2013 16:01:10

...

apply lag          47 minutes          28174 07/01/2013 05:14:53

apply lag          48 minutes          28231 07/01/2013 05:14:49

apply lag          49 minutes          27099 07/01/2013 05:14:44

apply lag          50 minutes          26532 07/01/2013 05:14:40

...

apply lag           3 hours           564493 07/01/2013 05:00:08

apply lag           4 hours           511628 06/22/2013 07:43:26

apply lag           5 hours           448572 06/22/2013 07:34:03

apply lag           6 hours           369037 06/22/2013 07:09:59

apply lag           7 hours           206117 06/21/2013 00:53:27

apply lag           8 hours           137932 06/21/2013 00:33:53

apply lag           9 hours           137091 06/21/2013 00:03:33

apply lag          10 hours            98103 06/20/2013 23:26:34

apply lag          11 hours           104157 06/20/2013 22:53:12

apply lag          12 hours           102141 06/20/2013 22:14:07

apply lag          13 hours            89214 06/20/2013 21:32:22

apply lag          14 hours            64880 06/20/2013 21:04:29

apply lag          15 hours            43471 06/20/2013 21:01:45

apply lag          16 hours            38075 06/20/2013 20:59:37

apply lag          17 hours            38449 06/20/2013 20:55:34

apply lag          18 hours            22049 06/16/2013 01:22:55

apply lag          19 hours            19873 06/16/2013 00:53:55

apply lag          20 hours            15985 06/15/2013 23:52:16

apply lag          21 hours            13290 06/15/2013 03:08:49

apply lag          22 hours             7330 06/15/2013 02:07:26

apply lag          23 hours             1606 02/15/2013 22:16:11

apply lag           1 days              3216 02/15/2013 22:00:42

apply lag           2 days             16768 02/15/2013 20:54:06

144 rows selected.

• Redo switch report of primary database can be seen with the following query. This information may be helpful when investigating the possible causes of archive gaps, apply lags or data guard performance issues.

SQL> SET PAGESIZE 9999

SQL> col day format a15

SQL> SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;

DAY                 COUNT#       MIN#       MAX# DAILY_AVG_MB

--------------- ---------- ---------- ---------- ------------

2013-07-01             442     147345     147566       452608

2013-06-30             526     147083     147347       538624

2013-06-29             532     146817     147082       544768

2013-06-28             928     146353     146816       950272

2013-06-27             760     145973     146352       778240

2013-06-26             708     145619     145972       724992

2013-06-25             560     145338     145618       573440

2013-06-24             498     145090     145339       509952

2013-06-23             104     145038     145089       106496

2013-06-22             338     144869     145037       346112

2013-06-21             748     144495     144868       765952

2013-06-20             748     144121     144494       765952

2013-06-19             952     143645     144120       974848

2013-06-18             882     143204     143644       903168

2013-06-17             914     142746     143203       935936

2013-06-16             454     142520     142747       464896

2013-06-15            1520     141760     142519      1556480

2013-06-14            1862     140829     141759      1906688

2013-06-13             970     140343     140828       993280

2013-06-12             598     140045     140345       612352

2013-06-11             550     139770     140044       563200

2013-06-10             516     139511     139769       528384

2013-06-09             178     139423     139512       182272

2013-06-08             296     139275     139422       303104

2013-06-07             490     139030     139274       501760

2013-06-06             572     138744     139029       585728

2013-06-05             488     138499     138743       499712

2013-06-04             554     138223     138500       567296

• The last one is a shell command and lists the archive log apply records of standby database alert log with the corresponding times at the end of the line. This is useful to see a clean picture of redo apply status on the standby database.

tail -10000 /u01/app/oracle/product/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'

Media Recovery Log +DATA/…/thread_1_seq_69468.904.819643305 Mon Jul 01 14:42:14 2013

Media Recovery Log +DATA/…/thread_1_seq_69469.899.819643701 Mon Jul 01 14:48:51 2013

Media Recovery Log +DATA/…/thread_2_seq_78072.741.819643579 Mon Jul 01 14:49:24 2013

Media Recovery Log +DATA/…/thread_1_seq_69470.956.819643639 Mon Jul 01 14:50:30 2013

Media Recovery Log +DATA/…/thread_2_seq_78073.1129.819644003 Mon Jul 01 14:53:55 2013

Media Recovery Log +DATA/…/thread_1_seq_69471.1123.819643961 Mon Jul 01 14:54:10 2013

Media Recovery Log +DATA/…/thread_1_seq_69472.861.819644303 Mon Jul 01 14:58:54 2013

Media Recovery Log +DATA/…/thread_2_seq_78074.1136.819644507 Mon Jul 01 15:02:14 2013

Media Recovery Log +DATA/…/thread_1_seq_69473.1024.819644695 Mon Jul 01 15:05:24 2013

Media Recovery Log +DATA/…/thread_2_seq_78075.936.819644933 Mon Jul 01 15:09:25 2013

Media Recovery Log +DATA/…/thread_1_seq_69474.904.819645085 Mon Jul 01 15:11:57 2013

Media Recovery Log +DATA/…/thread_1_seq_69475.911.819645509 Mon Jul 01 15:19:01 2013

Media Recovery Log +DATA/…/thread_2_seq_78076.899.819645377 Mon Jul 01 15:19:17 2013

Media Recovery Log +DATA/…/thread_1_seq_69476.1018.819646001 Mon Jul 01 15:27:12 2013

Media Recovery Log +DATA/…/thread_2_seq_78077.1060.819645837 Mon Jul 01 15:27:53 2013

Media Recovery Log +DATA/…/thread_1_seq_69477.956.819645995 Mon Jul 01 15:28:48 2013

Media Recovery Log +DATA/…/thread_2_seq_78078.861.819646339 Mon Jul 01 15:32:50 2013

Media Recovery Log +DATA/…/thread_1_seq_69478.1123.819646363 Mon Jul 01 15:33:17 2013

Media Recovery Log +DATA/…/thread_1_seq_69479.741.819646705 Mon Jul 01 15:38:57 2013

Media Recovery Log +DATA/…/thread_2_seq_78079.890.819646767 Mon Jul 01 15:40:00 2013

Media Recovery Log +DATA/…/thread_1_seq_69480.904.819647027 Mon Jul 01 15:44:21 2013

Media Recovery Log +DATA/…/thread_2_seq_78080.911.819647307 Mon Jul 01 15:48:59 2013

Media Recovery Log +DATA/…/thread_1_seq_69481.1136.819647365 Mon Jul 01 15:49:57 2013

Media Recovery Log +DATA/…/thread_1_seq_69482.1018.819647679 Mon Jul 01 15:55:10 2013

Media Recovery Log +DATA/…/thread_2_seq_78081.936.819647855 Mon Jul 01 15:58:03 2013

Media Recovery Log +DATA/…/thread_1_seq_69483.1024.819648003 Mon Jul 01 16:00:35 2013