creating a view where there is a true identifier (serial_number) that is time ordered

ranked_view.sql

-- Create the devices table
DROP TABLE IF EXISTS devices;
CREATE TABLE devices (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    serial_number TEXT NOT NULL,
    baud INTEGER NOT NULL,
    UNIQUE(name)
);

-- Insert sample data into the devices table
INSERT INTO devices (id, name, serial_number, baud) VALUES
(1, 'Device A1', 'SN001', 9600),
(2, 'Device B1', 'SN002', 4800),
(3, 'Device A2', 'SN001', 19200),
(4, 'Device C1', 'SN003', 9600),
(5, 'Device B2', 'SN002', 115200),
(6, 'Device D1', 'SN004', 9600);

-- Create the view to get the latest device per serial number
DROP VIEW IF EXISTS latest_device_per_serial_number;
CREATE VIEW latest_device_per_serial_number AS
select * from devices GROUP BY "serial_number" HAVING "id"= MAX("id");

-- Query the view
SELECT * FROM latest_device_per_serial_number;