Re: How to get the position of each record in a SELECT statement

Поиск
Список
Период
Сортировка
От Stephen Tahmosh
Тема Re: How to get the position of each record in a SELECT statement
Дата
Msg-id SN2PR07MB25265D918CDA88BAFA12EA0EC4C60@SN2PR07MB2526.namprd07.prod.outlook.com
обсуждение исходный текст
Ответ на How to get the position of each record in a SELECT statement  (JORGE MALDONADO <jorgemal1960@gmail.com>)
Список pgsql-sql
Дерево обсуждения

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

THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intended recipient, your use of this message for any purpose is strictly prohibited. If you have received this communication in error, please delete the message without making any copies and notify the sender so that we may correct our records. Thank you.

В списке pgsql-sql по дате отправления: