From: Adelo Herrero Pérez <adelo.herrero@gmail.com>
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: How to get the position of each record in a SELECT statement
Date: Sat, 8 Oct 2016 09:35:58 +0200
Message-ID: <DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com> (raw)
In-Reply-To: <CAAY=A7_PdEeLWtQev0ZDnky9b4PzU1QHn0JYK4S5fS4qXG3kig@mail.gmail.com>
References: <CAAY=A7_PdEeLWtQev0ZDnky9b4PzU1QHn0JYK4S5fS4qXG3kig@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
El 07/10/2016, a las 19:20, JORGE MALDONADO <jorgemal1960@gmail.com> escribió:
> Let´s say that I have the following simple SELECT statement:
>
> SELECT first, id FROM customers ORDER BY first
>
> This would result in something like this:
> Charles C1001
> John A3021
> Kevin F2016
> Paul N4312
> Steve J0087
>
> Is it possible to include a "field" in the SELECT such that it represents the position of each record?
> For example, I need to get a result like this:
>
> 1 Charles C1001
> 2 John A3021
> 3 Kevin F2016
> 4 Paul N4312
> 5 Steve J0087
>
> Respectfully,
> Jorge Maldonado
Hi:
If you need the order in the result (not physically) can try this code:
SELECT
(SELECT COUNT(*)
FROM customers o
WHERE (o.first = c.first) and (o.id = c.id)) AS position,
c.first,
c.id
FROM customers c
order by c.first
Hope this help,
Best regards.
Attachments:
[application/pkcs7-signature] smime.p7s (2.4K, ../DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com/2-smime.p7s)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adelo.herrero@gmail.com, jorgemal1960@gmail.com
Subject: Re: How to get the position of each record in a SELECT statement
In-Reply-To: <DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox