SQL Querying Data

*Table structures are located at the bottom.

A. Select All Records 

SELECT *
FROM pages;
Example Result Set
id author_id title content created_at updated_at
1023 132 Tail of Two Cities It happened on a stary night.... 2018-03-15T12:45:32 2018-03-17T09:15:38
1024 56 A Christmas Carol Twas the night before Christmas, all was quiet.... 2019-12-20T20:01:11 2019-12-20T20:01:11
...continues until all records of `pages` are provided

B. Filtering Records 

SELECT *
FROM pages
WHERE created_at > '2020-01-01' AND author_id = 45;
Example Result Set
id author_id title content created_at updated_at
578 45 The Bear and the Dragon There once was a Bear and a Dragon.... 2000-09-02T12:43:01 2000-09-02T12:43:01
599 45 Red Rabbit See the red rabbit run and you will never.... 2002-07-18T04:43:01/td> 2002-07-18T04:43:01
834 45 The Teeth of the Tiger The tiger was crouched down, waiting to pounce.... 2003-06-22T02:00:55 2003-06-22T02:00:55

C. Select all records with additional data from second table 

SELECT pages.id as page_id, pages.title as page_title,
    users.email as author_email, CONCAT(users.first_name, ' ', users.last_name) as author_name
FROM pages
    JOIN users ON pages.author_id = users.id;
Example Result Set
page_id page_title author_email author_name
1023 Tail of Two Cities charles@dickens.com Charles Dickens
1024 A Christmas Carol charles@dickens.com Charles Dickens
1027 Tom Sawyer sam@marktwain.com Mark Twain
1029 For Whom The Bell Tolls ernest@hemingway.com Ernest Hemingway
...continues until all records of `pages` are provided

D. Select latest record, grouped by a column

All 3 queries provide the same results.

SELECT c.id, c.page_id, c.author_id, c.content, c.created_at
FROM comments c
  LEFT JOIN comments lj_c ON (c.page_id = lj_c.page_id AND c.created_at < lj_c.created_at)
WHERE lj_c.page_id IS NULL;

WITH last_records AS (
  SELECT comments.*, ROW_NUMBER() OVER (PARTITION BY page_id ORDER BY created_at DESC) AS rec_num
  FROM comments AS c
)
SELECT id, page_id, author_id, content, created_at
FROM last_records WHERE rec_num = 1;
Example Result Set
id page_id author_id content created_at
24575 1023 2343 It was the worst of times:) 2024-08-12T19:34:01
24689 1024 2377 Ba Hum Bug! 2024-12-24T19:34:01
25002 1027 2343 Whitewashing fences is fun 2025-03-05T17:46:34
25003 1029 334 Light that bomb and run! 2025-06-25T14:36:24
...continues until pages with comments have 1 record provided

Table Schema

pages
  • id (bigint auto_increment primary key)
  • author_id (bigint not null)
  • title (varchar(128) not null)
  • content (text not null)
  • created_at (timestamp not null)
  • updated_at (timestamp not null)
users
  • id (bigint auto_increment primary key)
  • email (varchar(128) not null)
  • first_name (varchar(128) null)
  • last_name (varchar(128) null)
comments
  • id (bigint auto_increment primary key)
  • page_id (bigint not null)
  • parent_id (bigint null)
  • author_id (bigint not null)
  • content (text not null)
  • created_at (timestamp not null)
  • updated_at (timestamp not null)
images
  • id (bigint auto_increment primary key)
  • name (varchar(128) not null)
  • src (varchar(128) not null)
  • size (int not null)
  • width (decimal(8,2) null)
  • height (decimal(8,2) null)
page_image
  • page_id (bigint not null)
  • image_id (bigint not null)
  • status (varchar(128) not null)