Languages
[Edit]
EN

Node.js - PostgreSQL - select last N rows

0 points
Created by:
Broncono
316

In this article, we would like to show you how to select the lastΒ N rows in the PostgreSQL database usingΒ Node.js.

Node.js - PostgreSQL - select last N rows
Data used in the example - HeidiSQL

Note:Β at the end of this article you can find database preparation SQL queries.

Β Ascending order

const { Client } = require('pg');

const client = new Client({
    host: '127.0.0.1',
    user: 'postgres',
    database: 'database_name',
    password: 'password',
    port: 5432,
});

const getLastUsers = async (count) => {
	const query = `
		    SELECT * FROM (
		    	SELECT * FROM "users" 
		    	ORDER BY "id" DESC
		    	LIMIT $1
		    ) subquery
		    ORDER BY "id" ASC;
    `;
    try {
		
        await client.connect(); // gets connection
        const { rows } = await client.query(query, [count]); // sends query
        console.table(rows);
    } catch (error) {
        console.error(error.stack);
    } finally {
        await client.end();     // closes connection
    }
};

getLastUsers(3); // get last 3 users in ascending order

Result:Β 

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚ (index) β”‚ id β”‚  name   β”‚  country  β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚    0    β”‚ 5  β”‚ 'Marco' β”‚  'Italy'  β”‚
β”‚    1    β”‚ 6  β”‚ 'Kate'  β”‚  'Spain'  β”‚
β”‚    2    β”‚ 7  β”‚  'Nam'  β”‚ 'Vietnam' β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Descending order

const { Client } = require('pg');

const client = new Client({
    host: '127.0.0.1',
    user: 'postgres',
    database: 'database_name',
    password: 'password',
    port: 5432,
});

const getLastUsers = async (count) => {
	const query = `SELECT * 
                   FROM "users" 
		           ORDER BY "id" DESC 
                   LIMIT $1;`;
    try {
        await client.connect(); // gets connection
        const { rows } = await client.query(query, [count]); // sends query
        console.table(rows);
    } catch (error) {
        console.error(error.stack);
    } finally {
        await client.end();     // closes connection
    }
};

getLastUsers(3); // get last 3 users in descending order

Result:Β 

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚ (index) β”‚ id β”‚  name   β”‚  country  β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚    0    β”‚ 7  β”‚  'Nam'  β”‚ 'Vietnam' β”‚
β”‚    1    β”‚ 6  β”‚ 'Kate'  β”‚  'Spain'  β”‚
β”‚    2    β”‚ 5  β”‚ 'Marco' β”‚  'Italy'  β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Database preparation

create_tables.sqlΒ file:

CREATE TABLE "users" (
	"id" SERIAL,
	"name" VARCHAR(100) NOT NULL,
	"country" VARCHAR(15) NOT NULL,
	PRIMARY KEY ("id")
);

insert_data.sqlΒ file:

INSERT INTO "users"
	("name", "country")
VALUES
    ('Tom', 'Poland'),
    ('Chris', 'Spain'),
    ('Jack', 'Spain'),
    ('Kim', 'Vietnam'),
    ('Marco', 'Italy'),
    ('Kate', 'Spain'),
    ('Nam', 'Vietnam');

Native SQL query (used in the above example):

Ascending order

SELECT * FROM (
    SELECT * FROM `users` 
	 ORDER BY `id` DESC
	 LIMIT 3
) subquery
ORDER BY `id` ASC;

Descending order

SELECT * 
FROM `users` 
ORDER BY `id` DESC 
LIMIT 3;

Node.js - PostgreSQL - Problems

Native Advertising
πŸš€
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

β€οΈπŸ’» πŸ™‚

Join