agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

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:44:41 +0200
Message-ID: <1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com> (raw)
In-Reply-To: <DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com>
References: <CAAY=A7_PdEeLWtQev0ZDnky9b4PzU1QHn0JYK4S5fS4qXG3kig@mail.gmail.com>
	<DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>


El 08/10/2016, a las 09:35, Adelo Herrero Pérez <adelo.herrero@gmail.com> escribió:

> 
> 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.
> 
> 

Sorry, the correct code is:

SELECT 
      (SELECT COUNT(*)
         FROM customers o
        WHERE o.first <= c.first) AS position,
      c.first,
      c.id    
 FROM customers c 
 order by c.first

Best regards.=

Attachments:

  [application/pkcs7-signature] smime.p7s (2.4K, ../1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com/3-smime.p7s)
  download

view thread (4+ messages)

Message-ID: <1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com>
Permalink:  ../1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com/
Also on:    postgresql.org/message-id/1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com

reply

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: <1C7819B0-3586-430E-8D9F-7B2C1836F6A6@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox