EN
Node.js - PostgreSQL - select last N rows
0 points
In this article, we would like to show you how to select the last N rows in the PostgreSQL database using Node.js.
Note: at the end of this article you can find database preparation SQL queries.
xxxxxxxxxx1
const { Client } = require('pg');2
3
const client = new Client({4
host: '127.0.0.1',5
user: 'postgres',6
database: 'database_name',7
password: 'password',8
port: 5432,9
});10
11
const getLastUsers = async (count) => {12
const query = `13
SELECT * FROM (14
SELECT * FROM "users" 15
ORDER BY "id" DESC16
LIMIT $117
) subquery18
ORDER BY "id" ASC;19
`;20
try {21
22
await client.connect(); // gets connection23
const { rows } = await client.query(query, [count]); // sends query24
console.table(rows);25
} catch (error) {26
console.error(error.stack);27
} finally {28
await client.end(); // closes connection29
}30
};31
32
getLastUsers(3); // get last 3 users in ascending orderResult:
xxxxxxxxxx1
┌─────────┬────┬─────────┬───────────┐2
│ (index) │ id │ name │ country │3
├─────────┼────┼─────────┼───────────┤4
│ 0 │ 5 │ 'Marco' │ 'Italy' │5
│ 1 │ 6 │ 'Kate' │ 'Spain' │6
│ 2 │ 7 │ 'Nam' │ 'Vietnam' │7
└─────────┴────┴─────────┴───────────┘xxxxxxxxxx1
const { Client } = require('pg');2
3
const client = new Client({4
host: '127.0.0.1',5
user: 'postgres',6
database: 'database_name',7
password: 'password',8
port: 5432,9
});10
11
const getLastUsers = async (count) => {12
const query = `SELECT * 13
FROM "users" 14
ORDER BY "id" DESC 15
LIMIT $1;`;16
try {17
await client.connect(); // gets connection18
const { rows } = await client.query(query, [count]); // sends query19
console.table(rows);20
} catch (error) {21
console.error(error.stack);22
} finally {23
await client.end(); // closes connection24
}25
};26
27
getLastUsers(3); // get last 3 users in descending orderResult:
xxxxxxxxxx1
┌─────────┬────┬─────────┬───────────┐2
│ (index) │ id │ name │ country │3
├─────────┼────┼─────────┼───────────┤4
│ 0 │ 7 │ 'Nam' │ 'Vietnam' │5
│ 1 │ 6 │ 'Kate' │ 'Spain' │6
│ 2 │ 5 │ 'Marco' │ 'Italy' │7
└─────────┴────┴─────────┴───────────┘create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "users" (2
"id" SERIAL,3
"name" VARCHAR(100) NOT NULL,4
"country" VARCHAR(15) NOT NULL,5
PRIMARY KEY ("id")6
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "users"2
("name", "country")3
VALUES4
('Tom', 'Poland'),5
('Chris', 'Spain'),6
('Jack', 'Spain'),7
('Kim', 'Vietnam'),8
('Marco', 'Italy'),9
('Kate', 'Spain'),10
('Nam', 'Vietnam');Ascending order
xxxxxxxxxx1
SELECT * FROM (2
SELECT * FROM `users` 3
ORDER BY `id` DESC4
LIMIT 35
) subquery6
ORDER BY `id` ASC;Descending order
xxxxxxxxxx1
SELECT * 2
FROM `users` 3
ORDER BY `id` DESC 4
LIMIT 3;