Обсуждение: How to get the position of each record in a SELECT statement

Поиск
Список
Период
Сортировка

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

От
JORGE MALDONADO
Дата:
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

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

От
Stephen Tahmosh
Дата:

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.

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

От
Adelo Herrero Pérez
Дата:
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.

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

От
Adelo Herrero Pérez
Дата:

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.
Вложения

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.