<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ping Charoenwet on Medium]]></title>
        <description><![CDATA[Stories by Ping Charoenwet on Medium]]></description>
        <link>https://medium.com/@PingCh?source=rss-659860c8e297------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*2TlzVBSRlWYoOaqt.</url>
            <title>Stories by Ping Charoenwet on Medium</title>
            <link>https://medium.com/@PingCh?source=rss-659860c8e297------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 22:04:59 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@PingCh/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[My Standing Desk Journey — A Guide to Fix Any Top on IKEA Skarsta]]></title>
            <link>https://medium.com/@PingCh/my-standing-desk-journey-a-guide-to-fix-any-top-on-ikea-skarsta-65f36f79bfb5?source=rss-659860c8e297------2</link>
            <guid isPermaLink="false">https://medium.com/p/65f36f79bfb5</guid>
            <category><![CDATA[skarsta]]></category>
            <category><![CDATA[hacks]]></category>
            <category><![CDATA[standing-desk]]></category>
            <category><![CDATA[ikea]]></category>
            <dc:creator><![CDATA[Ping Charoenwet]]></dc:creator>
            <pubDate>Sat, 04 Sep 2021 15:41:33 GMT</pubDate>
            <atom:updated>2021-09-05T02:04:27.547Z</atom:updated>
            <content:encoded><![CDATA[<h3>My Standing Desk Journey — A Guide to Fix Any Top on IKEA Skarsta</h3><p>Standing desk was in my spotlight for years but never made it to the actionable priority. Until working from home era forced me to do just that, in a peculiar way.</p><p>After full year of sitting 40 hours a week (mostly 60, but ok) in front of the makeshift workstation made of a cheap folding table and my 10 years old sliding chair, my neck shoulder and back took control of my brain and decided to invest in my most expensive office furniture ever.</p><p>Demand and supply showed their true colors in this market. Earlier last year the price of electric motorized standing desk in Thailand was around the high-end of 1x,000THB. An IKEA <a href="https://www.ikea.com/th/th/p/bekant-desk-sit-stand-white-s19291344/?utm_source=google&amp;utm_medium=surfaces&amp;utm_campaign=shopping_feed&amp;utm_content=free_google_shopping_clicks_Workspaces&amp;gclid=Cj0KCQjwssyJBhDXARIsAK98ITRXaigBLt93iBpRgl_5rKr0LbMv4nf6ishgkqrqYJ9oaxopJenam6saArKEEALw_wcB&amp;gclsrc=aw.ds">Bekant</a> motorized standing desk’s tag price was 22,000THB, before dropping to 15,000 - 17,000THB very recently, which is far behind to compete with local brands in the range of 13,000THB plus/minus. Even these local ones were dropped to 11,000THB at the lowest due to more number of sellers.</p><p>Frugal to the core, I opted for the hand-crank option instead of these models. The famous IKEA’s <a href="https://www.ikea.com/th/en/p/skarsta-desk-sit-stand-beige-white-s89320814/">Skarsta</a> was my aim. The full set costs around 7,000THB which is reasonably friendly than the others, and I can spend a few more calories manually cranking it up-and-down, so why not? But demand and supply played the trick again, none of those were available since I started looking for one in February (yes, I kept monitoring them).</p><p>Fast forward to July, (bare in mind I was waiting since February), I saw the little green dot on IKEA website while slacking from work. It’s the Skarsta’s <a href="https://www.ikea.com/th/en/p/skarsta-underframe-sit-stand-f-table-top-white-30458554/">underframe</a>. I made an order without thinking, hoping the top would come in few weeks. <strong>**Spoiler alert**</strong>: It didn’t.</p><figure><img alt="never thought I would be this happy to see the green dot" src="https://cdn-images-1.medium.com/max/620/1*JgX0sAtS7HPIRh9oKRmtYg.png" /><figcaption>never thought I would be this happy to see the green dot</figcaption></figure><p>Last week of August, I called IKEA to ask for restocking. “We have some problem with supplier. You may recheck in November but the chance is slim” they said. Hopeless, I searched for an alternative and randomly found an interesting <a href="https://gist.github.com/mdo/150b7d8b666a114831ab3908d74ee424">thread</a> where someone was fitting a Gerton top on Bekant. Or if you’re in the UK, you can order a solid top for Skarsta from this <a href="https://gist.github.com/mdo/150b7d8b666a114831ab3908d74ee424">website</a>. None of these seem applicable in my context.</p><p>With the frame sitting in my house for two months, I must do something. Here’s what I did (I’ll put the list of items again at the end):</p><ul><li>The original Skarsta top is either 120cm x 70cm or 160cm x 80cm. So I ordered a <a href="https://www.lazada.co.th/products/i1412194273-s3627838431.html?urlFlag=true&amp;mp=1&amp;spm=spm%3Da2o4m.order_details.item_title.1">joint rubber wood plank</a> 130cm x 70cm just to make it unique. (first time ordering a piece of wood online, an eclectic experience)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8q_bRarSXiYoEI46fNI32g.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M6ZPXe9N8ra_vI_9jAq0hg.jpeg" /><figcaption>arrived safe and sound within 3 days</figcaption></figure><ul><li>The top came unfinished. I looked up the methods and found that I can use water-based Polyurethane to coat the surface without mixing it with any solvent. Went to the store near by and got a small gallon (1L) of <a href="https://www.homepro.co.th/p/1023173">matte Polyurethane</a>, fine <a href="https://www.homepro.co.th/p/255630">sandpaper</a> (no 360), and a <a href="https://www.homepro.co.th/p/1158501">brush</a>. The trick is to paint the first layer as thin as possible, then sanding it well. The second and third layer can be thicker, keep sanding and dry-cleaning the surface between each layer. Better do it in the sunny day.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TH6TG5GLvysbcsoCt1BzXg.jpeg" /><figcaption>to watch the paint dry is actually awesome</figcaption></figure><ul><li>The most worrying part is the fastening. I followed an idea from the thread about Gerton top with Bekant frame by placing the frame up-side-down- over the table top and mark all the drilling points. Needless to say, this has to be as precise as possible.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HU2Yp3PFTYJdcKpoFcMF3g.jpeg" /><figcaption>mark 12 holes with pencil, fill the circle well</figcaption></figure><ul><li>To get the right measurement, I brought original screws that came with the frame to the store and ask the staff to find the wood nuts that fit them. It turned out that Skarsta 2020 use twelve of 6mm metal screws to attach the top. So I got these 6mm x 13mm <a href="https://www.homepro.co.th/p/1120767">wood nuts</a> and a 10mm drill head (like <a href="http://www.whitemktstore.com/product/16730/%E0%B8%94%E0%B8%AD%E0%B8%81%E0%B8%AA%E0%B8%A7%E0%B9%88%E0%B8%B2%E0%B8%99%E0%B9%80%E0%B8%88%E0%B8%B2%E0%B8%B0%E0%B9%84%E0%B8%A1%E0%B9%89-%E0%B8%81%E0%B9%89%E0%B8%B2%E0%B8%99%E0%B8%95%E0%B8%A3%E0%B8%87-%E0%B8%82%E0%B8%99%E0%B8%B2%E0%B8%94-10-mm-d-07098-%E0%B8%A3%E0%B8%B8%E0%B9%88%E0%B8%99-mp008980-%E0%B8%A2%E0%B8%B5%E0%B9%88%E0%B8%AB%E0%B9%89%E0%B8%AD-makita">this</a>) to poke the holes. There’s no need to use hammer drill on the wood, even a small IKEA <a href="https://www.ikea.com/th/en/p/fixa-screwdriver-drill-li-ion-50214206/">Fixa</a> screwdriver can get the job done. During the drill, don’t forget to mark the depth with some duct <a href="https://www.youtube.com/watch?v=fA3sveDhHYs">tape</a>. In my case the wood is 20mm thick, so I’d drilled 12mm and hammer the nuts down to fit the surface.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n_X9f0k0nyKRWPW9MqXmJw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2x8eidko09G5TR8Hie-SmA.jpeg" /><figcaption>not the most neat wood work and you may notice the last unfit hole, but anyway</figcaption></figure><p><strong>Final BOM</strong>:</p><ul><li>Major material: <a href="https://www.lazada.co.th/products/i1412194273-s3627838431.html?urlFlag=true&amp;mp=1&amp;spm=spm%3Da2o4m.order_details.item_title.1">Joint rubber wood plank</a> 130cm x 70cm -&gt; biggest Skarsta’s top dimension is 160cm x 80cm, any size within these range should fit. Just be precise and center-align everything when marking the holes. On the weight, Skarsta’s maximum load is around 60kg without a top. Simply calculating that, you can figure out the remaining load for your screens after applying preferred top.</li><li>Surface work: <a href="https://www.homepro.co.th/p/1023173">matte Polyurethane</a>, <a href="https://www.homepro.co.th/p/255630">sandpaper</a> (no 360 or no 320), a <a href="https://www.homepro.co.th/p/1158501">brush</a> -&gt; this can vary by the material you choose for the top.</li><li>Attachment: 6mm x 13mm <a href="https://www.homepro.co.th/p/1120767">wood nuts</a>, 10mm <a href="http://www.whitemktstore.com/product/16730/%E0%B8%94%E0%B8%AD%E0%B8%81%E0%B8%AA%E0%B8%A7%E0%B9%88%E0%B8%B2%E0%B8%99%E0%B9%80%E0%B8%88%E0%B8%B2%E0%B8%B0%E0%B9%84%E0%B8%A1%E0%B9%89-%E0%B8%81%E0%B9%89%E0%B8%B2%E0%B8%99%E0%B8%95%E0%B8%A3%E0%B8%87-%E0%B8%82%E0%B8%99%E0%B8%B2%E0%B8%94-10-mm-d-07098-%E0%B8%A3%E0%B8%B8%E0%B9%88%E0%B8%99-mp008980-%E0%B8%A2%E0%B8%B5%E0%B9%88%E0%B8%AB%E0%B9%89%E0%B8%AD-makita">drill head</a>, an electric <a href="https://www.ikea.com/th/en/p/fixa-screwdriver-drill-li-ion-50214206/">screwdriver</a> of your choice -&gt; smaller nuts should fit as well if you place flat washers under the smaller screws and tighten them to the frame. But are you going to waste all those 12 standard screws?</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xp5ce__HfUv2N-JP8nl6xw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UbfFDKe0oNqXQAAl4yPNxQ.jpeg" /><figcaption>next stop is inevitably the monitor holder…</figcaption></figure><p>The total cost for the top came at around 1,600THB (without the Fixa screwdriver, of course). In comparison to <a href="https://www.ikea.com/th/en/p/skarsta-table-top-beige-40458563/">original one</a> at 2,000THB without the plan to restock, I consider this a win. Would you agree?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=65f36f79bfb5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Realtime Classroom Consensus Vote & Monitor with Firebase — Quick and Dirty Development]]></title>
            <link>https://medium.com/@PingCh/realtime-classroom-consensus-vote-monitor-with-firebase-quick-and-dirty-development-fe9640c02303?source=rss-659860c8e297------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe9640c02303</guid>
            <category><![CDATA[prototype]]></category>
            <category><![CDATA[vote]]></category>
            <category><![CDATA[classroom]]></category>
            <category><![CDATA[firebase]]></category>
            <category><![CDATA[meeting-rooms]]></category>
            <dc:creator><![CDATA[Ping Charoenwet]]></dc:creator>
            <pubDate>Thu, 02 Apr 2020 10:06:01 GMT</pubDate>
            <atom:updated>2020-04-02T10:21:30.949Z</atom:updated>
            <content:encoded><![CDATA[<h3>Realtime Classroom Consensus Vote &amp; Monitor with Firebase — Quick and Dirty Development</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vRySL1_Ln-fgL72F" /><figcaption>Photo by <a href="https://unsplash.com/@iamfelicia?utm_source=medium&amp;utm_medium=referral">Felicia Buitenwerf</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Do you ever want to tweak some features on Kahoot? It’s time now.</p><p><strong>TL;DR</strong> — We’re going to create small tool for classroom (or something of sort - imagine the settings of group meeting) that allows people ‘in class’ to make consensus vote on some choices. Each person will be given a certain amount of points so they can spend on whatever choices they prefer. But we still need some control.. (obviously), so the class moderator (so called ‘class master’)has the right to start/pause/resume/end session and control time window to accept points for each choice. Here’s the <a href="https://github.com/phnx/firebase-class-consensus">code</a>.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2FKDzsy4V1WTKrrmdr78%2Ftwitter%2Fiframe&amp;display_name=Giphy&amp;url=https%3A%2F%2Fgiphy.com%2Fgifs%2FKDzsy4V1WTKrrmdr78&amp;image=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2FKDzsy4V1WTKrrmdr78%2Fgiphy.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=giphy" width="435" height="244" frameborder="0" scrolling="no"><a href="https://medium.com/media/efc527d33c864f1fbec9cdbcf2a3e1e6/href">https://medium.com/media/efc527d33c864f1fbec9cdbcf2a3e1e6/href</a></iframe><p>This was a project I worked on awhile ago which hasn’t made it anywhere close to production. Since I’m now in <a href="https://www.who.int/publications-detail/considerations-for-quarantine-of-individuals-in-the-context-of-containment-for-coronavirus-disease-(covid-19)">self-isolation</a> and my WFH situation is going quite err.. slow, I think it’d be fun to dig this project out and turn it into an article for the sake of ‘technology’. I’m trying to write mostly in non-technical way (aka: <em>warning</em>, lots of requirement-ranting ahead).</p><h3>Activity Diagram</h3><p>Only feel like I can make a decent thing if eventually I can draw an activity diagram that makes sense.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/599/1*mMEFr-1vXZDx6QO5EWVJPw.png" /><figcaption>shouldn’t need rocket science to explain</figcaption></figure><p><strong>Prerequisites:</strong></p><ul><li>Firebase Real-Time Database (just a volatile data storage)</li><li>Firebase Hosting</li><li>jQuery (still does the job. sorry, no sorry)</li><li>No server-sided programs</li></ul><p>The system would consist of 3 modules.</p><ol><li>Landing page — Voter Screen [/index.html]</li><li>Backend page — Class Master Dashboard [/backend/dashboard.html]</li><li>Backend page — Monitor [/backend/monitor.html]</li></ol><p>Let’s break it down.</p><h3>Class Master Dashboard</h3><p>Everything starts here. I want a class master to access the backend and prepare the session before the class. They’ll need to enter session name, number of attendance, and options to be voted. In return, they’ll get a list of codes that must be given to each attendance/student/person right at the time of voting, because, you know, <strong><em>presence is essence</em></strong>. *wink*</p><p>After that, class master can start the session and proceed the option one-by-one. Just make sure that anyone who wanted to spend the points on this option did so.</p><p>What? need a quick pause? Sure, class master can make a short session pause. No one is allowed to casr a vote during that time, until the session is resumed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/272/1*S0260LLKHC-14lP4kG1EFw.gif" /><figcaption>Class master wouldn’t see realtime score on this screen. I found it’s too distracting.</figcaption></figure><h3>Voter Screen</h3><p>I considered the voter screen as the passive element of the system. They got nothing much to do. Just enter the given token code and wait for the vote to start. Then, vote on the options they like the most until they run out of points.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*nRZSgZog35WtQpdq136fgA.gif" /><figcaption>not sure about the performance if there’re over 100 voters..</figcaption></figure><p>At the end of session, voter won’t get the final result. It’s a system that encourage person-to-person communication. Ask your class master!</p><h3>Monitor.. just monitor</h3><p>Let’s say there’s one center screen/projector in the room. Why don’t we utilize it by showing realtime score? But I just multiply the score by 1,000 in monitor to exaggerate the whole thing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/536/1*fMncy0IZRXjiGMaq8I5P0A.gif" /><figcaption>*insert cash register machine sfx here madly*</figcaption></figure><h3>Implementation Highlights</h3><p>The <a href="https://github.com/phnx/firebase-class-consensus">code</a> speaks for itself (as I prototyped it in a few days (hours?), it may speak with a little bit heavy unclean accent..). But there’re some points worth mentioning.</p><ul><li>Realtime Database’s event callback is a real deal — it seems like a lifetime ago that this kind of message propagation was a magic endeavor. Now every client can have data <a href="https://firebase.google.com/docs/database#how_does_it_work">synchronization</a> that imitates push notification every time the data is updated. At least it does not cost you a fortune for tons of HTTP requests. Cool isn’t it?</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*BhNm-RPv5-AzHs906aoKwQ.gif" /><figcaption>message propagation used to be a pain in distributed system class of yesterday</figcaption></figure><ul><li>For system design, I rely on state machine — If you look into the code you’ll see that I tried to have objects on each module to interact through ‘state’. There’s less to no direct communication or chain reaction between objects. If you pull event listeners from each object, you’ll find some state elements at the bottom line; either local javascript states that control DOM or data states in Firebase Realtime Database that control data flow between modules. This kind of design simplifies the complex interactions in small systems, but I’m fully aware that it may or may not work as effective in the large scale systems.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/1*6Au_mc3jCHghYQb9c2DqMQ.png" /><figcaption>“vote_session/status” is sole controlling state for all clients</figcaption></figure><ul><li>I gave up some security for velocity — as I imagined the use case most of attendee would join the vote with their smartphone (or I’ll just provide the URL via QR code to reinforce that policy, and neglect that nerdy guy shooting QR code with a laptop’s camera). So there’s less possibility that they will try to lookup the api key to Firebase Realtime Database I added in the front-end. If this’s not the case, I’ll need to have all of them register and create authentication account.. which not only is a big implementation task but also requires more leading time before voting. However, all data is cleared every time session has ended so.. ¯\_(ツ)_/¯ .. But I still added the authentication rules with dummy email/password for class master’s session control, though.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wMYVPggaAwCVpgBfzac0NA.png" /><figcaption>One admin account for class master</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-TYST3o1kfwMWSd2HBaPEg.png" /><figcaption>vote_data is purposefully unprotected because attendees would only use provided tokens, not Firebase authentication credential</figcaption></figure><ul><li>Easy setup was an actual underlying requirement — I was initially building this tool for a client who produces TV program. They intended to use the program as ‘<a href="https://www.google.com/search?q=class+clicker&amp;source=lnms&amp;tbm=isch">clicker</a>’ in the studio. There was no data about expectable traffic so on and so forth. I needed to improvise and didn’t want to mess thing up with real-time transactions. With Firebase, I just include <a href="https://firebase.google.com/docs/web/setup">Firebase App and Firebase Database</a> in my bare bone HTML file and added following lines. Done. I can now be care free of server configuration and maintenance.</li></ul><pre>var config = {<br>      apiKey: &quot;{firebase-project-web-api-key}&quot;,<br>      authDomain: &quot;{firebase=project-auth-domain}&quot;,<br>      databaseURL: &quot;<a href="https://temp-dde7f.firebaseio.com/">{</a>firebase-realtime-database-url}&quot;,<br>      projectId: &quot;{project-id}&quot;,<br>    };</pre><pre>firebase.initializeApp(config);</pre><pre>var database = firebase.database();</pre><ul><li>Firebase testing and deployment is <strong>almost</strong> a killer — Firebase <a href="https://firebase.google.com/docs/hosting/deploying">document</a> is well-put. It allows developer to test all script locally and deploy to GCP all at once. However, <em>there’s a bummer</em>. To locally test your Realtime database instance, you have 3 options.<br>1) Connect to another endpoint entirely — meh<br>2) Use (somewhat complicated) <a href="https://firebase.google.com/docs/emulator-suite/connect_and_prototype?database=RTDB">emulator</a> — a bit out of scale for this small project<br>3) <a href="https://firebase.googleblog.com/2015/04/end-to-end-testing-with-firebase-server_16.html">Firebase server</a> — uh. no, thanks<br>or.. maybe 4)….</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/450/1*AzK1CWZUNg02yFB7E_rZxg.jpeg" /></figure><h3>Conclusion</h3><p>The prototype functions as planned. Still there are s̶o̶m̶e̶ lots of glitches. With these selected options of elements, it can only get close to ‘realtime’ but actually not. I occasionally found the random error when user submitted vote simultaneously with class master changing the choices. I assume that better front-end implementation (sorry jQuery) and the paid subscription to GCP should offer better latency and real ‘realtime’ manner. Not to mention the untested areas in my code. At the end of the day this, is yet another showcase of how quick &amp; slick GCP could provide you an off-the-shelf functionality. However, it’s definitely far from production-ready, let alone perfect.</p><h3>What next?</h3><p>There’re many potentials to make use of this prototype. If you’re hosting an online event with your friends or colleagues (perhaps during this pandemic), why not making a game out of it, so you can enjoy something else outside of your video conference screens? Keep a little fun going, and enjoy quick&amp;dirty programming ;)</p><h3>Final Disclaimer</h3><p>I casually stole many snippets, css, and images from stackoverflow, w3, and some free photo stocks respectively. Kudos to all of resource providers!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fe9640c02303" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Nth position within group (MySQL)- Quick and Dirty Programming]]></title>
            <link>https://medium.com/@PingCh/nth-position-within-group-mysql-quick-and-dirty-programming-5277f4cb8191?source=rss-659860c8e297------2</link>
            <guid isPermaLink="false">https://medium.com/p/5277f4cb8191</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[query]]></category>
            <category><![CDATA[quick-solution]]></category>
            <dc:creator><![CDATA[Ping Charoenwet]]></dc:creator>
            <pubDate>Sun, 08 Dec 2019 14:23:02 GMT</pubDate>
            <atom:updated>2019-12-14T05:28:54.598Z</atom:updated>
            <content:encoded><![CDATA[<h3>Nth position within group (MySQL) — Quick and Dirty Development</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*dvvh2RXiTno8Dfl-" /><figcaption>Photo by <a href="https://unsplash.com/@jizhidexiaohailang?utm_source=medium&amp;utm_medium=referral">Pocky Lee</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>One ordinary evening, there was an urgent request in a working group that I’ve been a member of. The requester called for somebody to craft some result from a given dataset. An hour passed, no one responded. I assumed it’s my turn to take an action. So here’s how the request and dataset looks like (already modified to classify <a href="https://en.wikipedia.org/wiki/Personal_data">PII</a> and working group’s identity, of course)…</p><figure><img alt="Sample Table with Sample data" src="https://cdn-images-1.medium.com/max/740/1*mNIWe3w-XZla1t9uBaRBIg.png" /></figure><blockquote>Now, the requirement: “We need to find people who got maximum cumulative score from each group”</blockquote><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c13655f602d9c94ccebfd2f0e8fa42f9/href">https://medium.com/media/c13655f602d9c94ccebfd2f0e8fa42f9/href</a></iframe><p>Straightforward. Some subqueries and joins should do the trick. I’d find the sum of scores per user, before subbing the query to get maximum value in each group, and return the accompanied <em>user_name </em>of that row. Case closed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*dVVKgJVDXDWQJQ3TPPbsgA.png" /><figcaption>Depiction of the initial plan, easy right?</figcaption></figure><p>Things never go as smooth as you expected. MySQL would return just the first <em>user_name </em>in each <em>user_group</em>, which it found from the whole aggregated table, <strong>not </strong>the exact row that has max score…</p><p>For this workaround, I created another subquery to summarize individual users’ score and match those scores with the maximum score from first subquery. Now, the query returned correct <em>user_id </em>of maximum scorers, which led to the final mapping of <em>user_name </em>and <em>user_group </em>as follows.</p><figure><img alt="Maximum scorer from each group" src="https://cdn-images-1.medium.com/max/740/1*oqX-uMhjAmNxwlit5p8Oyg.png" /></figure><p>Additionally, after quick discussion with the requester, it was found that <em>working_time </em>could be a flaw. The data was not really clean. Some filtering conditions must be added to remove glitches from data source. Subqueries grew a bit, but everything is still working just fine. The code below should always work with given test data.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9efbbba81b88ef797bb28a13e6d12d87/href">https://medium.com/media/9efbbba81b88ef797bb28a13e6d12d87/href</a></iframe><p>Next morning, a new requirement came just in time.</p><blockquote>Let’s also find the second top scorer. We need that list too.</blockquote><p>The tide has turned. MAX function no longer works in this context. After a bit of research I found a not-well-known magic function in MySQL called <a href="https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html">NTH_VALUE</a> which seems to be capable of partitioning a table with a designated column, arranging the entries with another column, then selectively returning a row of Nth order.</p><p>As far as I realized, this function has <a href="https://lefred.be/content/top-10-mysql-8-0-features-for-developers/">just appeared in MySQL 8.0</a> onwards, along with other small swiss-army-knife functions. ‘Keeping your database instance updated’ is the wise words, still.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3bd2a60adf97d42c62ce7131e5cabc6b/href">https://medium.com/media/3bd2a60adf97d42c62ce7131e5cabc6b/href</a></iframe><p>And here’s the result for when n = 2</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*i10dXarxpwNzSaqGuhTeSQ.png" /><figcaption>Second top scorer from each group</figcaption></figure><p>Wow.. Just Wow. (also notice the missing of second top scorer from <em>user_group</em> C. Unfortunately, the person was removed due to too small <em>working_time</em>. Showing that the filter condition still works.)</p><p>The Nth solution constantly gives the exact result as top scorer solution when replacing the ‘n’ parameter with ‘1’, meaning that it’s also a working alternative to the prior requirement.</p><p>That’s it. I’m wholeheartedly believing that this is still not the best solution for the requirement in terms of code readability, performance, or any database/programming practices. But it does get the job done, doesn’t it?</p><p>Let’s enjoy quick and dirty development</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5277f4cb8191" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>