pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get the position of each record in a SELECT statement
4+ messages / 3 participants
[nested] [flat]
* How to get the position of each record in a SELECT statement
@ 2016-10-07 17:20 JORGE MALDONADO <jorgemal1960@gmail.com>
2016-10-07 17:31 ` Re: How to get the position of each record in a SELECT statement Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-10-08 07:35 ` Re: How to get the position of each record in a SELECT statement Adelo Herrero Pérez <adelo.herrero@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: JORGE MALDONADO @ 2016-10-07 17:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get the position of each record in a SELECT statement
2016-10-07 17:20 How to get the position of each record in a SELECT statement JORGE MALDONADO <jorgemal1960@gmail.com>
@ 2016-10-07 17:31 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephen Tahmosh @ 2016-10-07 17:31 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get the position of each record in a SELECT statement
2016-10-07 17:20 How to get the position of each record in a SELECT statement JORGE MALDONADO <jorgemal1960@gmail.com>
@ 2016-10-08 07:35 ` Adelo Herrero Pérez <adelo.herrero@gmail.com>
2016-10-08 07:44 ` Re: How to get the position of each record in a SELECT statement Adelo Herrero Pérez <adelo.herrero@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Adelo Herrero Pérez @ 2016-10-08 07:35 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: 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.
Attachments:
[application/pkcs7-signature] smime.p7s (2.4K, ../../DC6D85EA-4B7E-424D-824E-A8D645E9BC33@gmail.com/2-smime.p7s)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get the position of each record in a SELECT statement
2016-10-07 17:20 How to get the position of each record in a SELECT statement JORGE MALDONADO <jorgemal1960@gmail.com>
2016-10-08 07:35 ` Re: How to get the position of each record in a SELECT statement Adelo Herrero Pérez <adelo.herrero@gmail.com>
@ 2016-10-08 07:44 ` Adelo Herrero Pérez <adelo.herrero@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Adelo Herrero Pérez @ 2016-10-08 07:44 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
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.=
Attachments:
[application/pkcs7-signature] smime.p7s (2.4K, ../../1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com/3-smime.p7s)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-10-08 07:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-07 17:20 How to get the position of each record in a SELECT statement JORGE MALDONADO <jorgemal1960@gmail.com>
2016-10-07 17:31 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-10-08 07:35 ` Adelo Herrero Pérez <adelo.herrero@gmail.com>
2016-10-08 07:44 ` Adelo Herrero Pérez <adelo.herrero@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox