Re: How to get the position of each record in a SELECT statement - Mailing list pgsql-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.