From: | Adelo Herrero Pérez <adelo(dot)herrero(at)gmail(dot)com> |
---|---|
To: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to get the position of each record in a SELECT statement |
Date: | 2016-10-08 07:44:41 |
Message-ID: | 1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
El 08/10/2016, a las 09:35, Adelo Herrero Pérez <adelo(dot)herrero(at)gmail(dot)com> escribió:
>
> El 07/10/2016, a las 19:20, JORGE MALDONADO <jorgemal1960(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ranjeet Verma | 2016-10-13 08:19:59 | Table Partitioning with Foreign Data Wrapper |
Previous Message | Adelo Herrero Pérez | 2016-10-08 07:35:58 | Re: How to get the position of each record in a SELECT statement |