Magento2 Database – How to Insert, Update, and Select Values in Custom Table

In this case,you need to create custom mysql code which will create mysql like this

Call your collection:

$collections = Mage::getResourceModel('module/namespace_collection');

Call the db resource singleton model

 $resource = Mage::getSingleton('core/resource');

Carry connection write connection

$writeConnection = $resource->getConnection('core_write');

Make a custom collection by calling write connection and make a custom subquery:

$subSelect =$writeConnection->select()
            ->from(
                array(' table2' => $collections->getTable('module/namespace')),
                array( 'sku', 'MaxDate'=>new Zend_Db_Expr("max(date)"))
            )
        ->group('table2.sku')

Join table

$collections->getSelect()->join(
                array(' tm' => new Zend_Db_Expr(sprintf('(%s)',$subSelect->assemble()))),
                'main_table.sku = tm.sku and main_table.date = tm.MaxDate,
                array()
            );

Select your selective columns

$collections->addFieldToSelect('sku')->addFieldToSelect('date');

Code is not tested and put basic of concept

Consider your table with title,content_heading and content field are defined,

Now you have to pass inside __consturct field to your module factory object like below, Table with below content are automatically added.

<?php
namespace Vendor\Modulename\Setup;

use Magento\Framework\Module\Setup\Migration;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

class InstallData implements InstallDataInterface
{
    private $_postFactory;

    public function __construct(\Vendor\Modulename\Model\PostFactory $postFactory)
    {
        $this->_postFactory = $postFactory;
    }


    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $data = [
            'title' => 'Hello world!',
            'content_heading' => 'Hello world!',
            'content' => 'logn description put here'
        ];

        $this->_postFactory->create()->setData($data)->save();
    }

}