Обсуждение: How to get the position of each record in a SELECT statement
While there is no literal “position” of each record in relational theory, the row_number() function might accomplish your requirement
You do have an order by so that must drive the position in your definition:
Select row_number() over (order by first) as “position”, first,id from customers order by first
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of JORGE MALDONADO
Sent: Friday, October 07, 2016 1:20 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to get the position of each record in a SELECT statement
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
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.
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.