Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bsmKz-0003r7-6a for pgsql-sql@arkaria.postgresql.org; Sat, 08 Oct 2016 07:46:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bsmKy-0004Et-Nm for pgsql-sql@arkaria.postgresql.org; Sat, 08 Oct 2016 07:46:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bsmJz-0003AS-Qe for pgsql-sql@postgresql.org; Sat, 08 Oct 2016 07:45:27 +0000 Received: from mail-wm0-x22b.google.com ([2a00:1450:400c:c09::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bsmJf-0001xG-12 for pgsql-sql@postgresql.org; Sat, 08 Oct 2016 07:45:27 +0000 Received: by mail-wm0-x22b.google.com with SMTP id i130so80412403wmg.1 for ; Sat, 08 Oct 2016 00:45:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=32824zSdAzWCa60GPqb9bYoryhJZQ1aj8Sccm4yGnWc=; b=jpBK342njluvx/uPfI+xfriPGonrn2G9RSBi1rI3F7vOX3T5imgPmPMX6qgI0PXmZZ XACPQyu3hzmRaI3UBhnCOeQT5HYkDvupj4rZ83xwlhrGm7aJ0QlEyyfc9X/D25jNgF+c OguAS7EslwOhBHjnoFZqq4LWIlPPGjNDpvidea4UT+DmOap7nc+cSKTOIhgIPtyCY4WC xZgEsk5i0GLRBueCjKeFxdL2YDs2KBrg8Av39wk12o4VS6mxvksbUq/69lv6UD7T24fy F81muzGDdtw2nv0GN71+6md3NTkbEByCTZ76qGDGujmfpwatyKk0pTnXd0clpjEuLIfZ 53Mg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=32824zSdAzWCa60GPqb9bYoryhJZQ1aj8Sccm4yGnWc=; b=ZHe0TlMf5ZayR1Q7scBZsxr81jqNLHbM+26TldId+D7ApXBuBSa2YlKcxe4MD4HWAs CimIK7yRXDrqHqL+2WONXyd/9DkCgTcDSqRaTxI/3rAxswLAhFh1jTPVMjjyxR+dBoHw MOD+pbmNwB0DFq7e0HregujWJSLxoz/cz7ojEWdLLuAYNfk1iuBRzSIJaFhiu2V8Bpkt gROetpAoVXNCPGp+6yd97E6aQHhKjewAc72d52vvURtn8ivgWJCwQcqjIe9z82xD+Av0 nCA/nTbIuoUeLZL5D0i6Wbdx2sof5j3/nWKxOHrw4+gh6z9O3MjpPddFedOzM3ii8i7I FCAQ== X-Gm-Message-State: AA6/9RnPPSKnjXrhXj7ogdWPlttrY/zlbhU84xE1tw5bMF2fO5fyv5uBirxUOjZWLnVJzA== X-Received: by 10.194.95.69 with SMTP id di5mr24856365wjb.54.1475912705830; Sat, 08 Oct 2016 00:45:05 -0700 (PDT) Received: from [192.168.200.6] ([185.110.226.163]) by smtp.gmail.com with ESMTPSA id bl3sm23585558wjc.26.2016.10.08.00.45.04 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Sat, 08 Oct 2016 00:45:05 -0700 (PDT) Content-Type: multipart/signed; boundary="Apple-Mail=_E514AF6C-616B-4FE2-860A-2409F23DEF06"; protocol="application/pkcs7-signature"; micalg=sha1 Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: How to get the position of each record in a SELECT statement From: =?iso-8859-1?Q?Adelo_Herrero_P=E9rez?= In-Reply-To: Date: Sat, 8 Oct 2016 09:44:41 +0200 Cc: "pgsql-sql@postgresql.org" Message-Id: <1C7819B0-3586-430E-8D9F-7B2C1836F6A6@gmail.com> References: To: JORGE MALDONADO X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail=_E514AF6C-616B-4FE2-860A-2409F23DEF06 Content-Type: multipart/alternative; boundary="Apple-Mail=_68F93A1E-3460-4E35-A963-CF8454BF87AA" --Apple-Mail=_68F93A1E-3460-4E35-A963-CF8454BF87AA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 El 08/10/2016, a las 09:35, Adelo Herrero P=E9rez = escribi=F3: >=20 > El 07/10/2016, a las 19:20, JORGE MALDONADO = escribi=F3: >=20 >> Let=B4s say that I have the following simple SELECT statement: >>=20 >> SELECT first, id FROM customers ORDER BY first >>=20 >> This would result in something like this: >> Charles C1001 >> John A3021 >> Kevin F2016 >> Paul N4312 >> Steve J0087 >>=20 >> 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: >>=20 >> 1 Charles C1001 >> 2 John A3021 >> 3 Kevin F2016 >> 4 Paul N4312 >> 5 Steve J0087 >>=20 >> Respectfully, >> Jorge Maldonado >=20 > Hi: >=20 > If you need the order in the result (not physically) can try this = code: >=20 > SELECT=20 > (SELECT COUNT(*) > FROM customers o > WHERE (o.first =3D c.first) and (o.id =3D c.id)) AS position, > c.first, > c.id =20 > FROM customers c=20 > order by c.first >=20 > Hope this help, > Best regards. >=20 >=20 Sorry, the correct code is: SELECT=20 (SELECT COUNT(*) FROM customers o WHERE o.first <=3D c.first) AS position, c.first, c.id =20 FROM customers c=20 order by c.first Best regards.= --Apple-Mail=_68F93A1E-3460-4E35-A963-CF8454BF87AA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1
El 08/10/2016, a las 09:35, Adelo = Herrero P=E9rez <adelo.herrero@gmail.com> = escribi=F3:


El 07/10/2016, a las 19:20, JORGE MALDONADO <jorgemal1960@gmail.com> = escribi=F3:

Let=B4s 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 =3D = c.first) and (o.id =3D 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 <=3D c.first) AS = position,
  =     c.first,
      c.id    
 FROM customers c 
 order by = c.first

Best = regards.
= --Apple-Mail=_68F93A1E-3460-4E35-A963-CF8454BF87AA-- --Apple-Mail=_E514AF6C-616B-4FE2-860A-2409F23DEF06 Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGnzCCBpsw ggWDoAMCAQICEBiGMlW4uHy1Vwdmc+5CpuYwDQYJKoZIhvcNAQELBQAwSzELMAkGA1UEBhMCRVMx ETAPBgNVBAoMCEZOTVQtUkNNMQ4wDAYDVQQLDAVDZXJlczEZMBcGA1UEAwwQQUMgRk5NVCBVc3Vh cmlvczAeFw0xNjA0MDgwODA2MTFaFw0yMDA0MDgwODA2MTFaMHMxCzAJBgNVBAYTAkVTMRIwEAYD VQQFEwk3MzY1NDM5MUwxFjAUBgNVBAQMDUhFUlJFUk8gUEVSRVoxDjAMBgNVBCoMBUFERUxPMSgw JgYDVQQDDB9IRVJSRVJPIFBFUkVaIEFERUxPIC0gNzM2NTQzOTFMMIIBIjANBgkqhkiG9w0BAQEF AAOCAQ8AMIIBCgKCAQEA3oVGdiwMEe3QKvdYjEFgwCmxX9M/vA1reuoU11MQT28EdbHFMycZ9VRl nvdoYXC5iT4/RQ2TxF/Fd4JNk1RfXHa7gL0I/pnwQfdBqlUU6s3dizueFkzDA9WoNZ8rVWkVzI61 HHqMyyGjim1AiT1rjuf5V0kK2iKIBER/X2I5uja2rTEjDBgYhZA/FM6PBMwFHpko5OHVNay8ld3b F2nEMsL+QHAfbHZzihxYzQCE9TBmP8Y/AwlpAQLvNQB/rBBzgwa97hag3+u1fpUdH2ubUV0tIAId PlJUI8LunX7Fc1XjjGh8fxF9qE7GqS5ZNF5qOEFIYAIlum5ZxcANhxZTswIDAQABo4IDUTCCA00w gYQGA1UdEQR9MHuBF2FkZWxvLmhlcnJlcm9AZ21haWwuY29tpGAwXjEYMBYGCSsGAQQBrGYBBAwJ NzM2NTQzOTFMMRQwEgYJKwYBBAGsZgEDDAVQRVJFWjEWMBQGCSsGAQQBrGYBAgwHSEVSUkVSTzEU MBIGCSsGAQQBrGYBAQwFQURFTE8wDAYDVR0TAQH/BAIwADAOBgNVHQ8BAf8EBAMCBeAwIwYDVR0l BBwwGgYIKwYBBQUHAwQGCCsGAQUFBwMCBgRVHSUAMB0GA1UdDgQWBBQdQJOd6hLD5nMomWovxfPH fH0a4TAfBgNVHSMEGDAWgBSx1E/EI3n6RAUJxus5z+g1sLggZDCBggYIKwYBBQUHAQEEdjB0MD0G CCsGAQUFBzABhjFodHRwOi8vb2NzcHVzdS5jZXJ0LmZubXQuZXMvb2NzcHVzdS9PY3NwUmVzcG9u ZGVyMDMGCCsGAQUFBzAChidodHRwOi8vd3d3LmNlcnQuZm5tdC5lcy9jZXJ0cy9BQ1VTVS5jcnQw gd0GA1UdIASB1TCB0jCBzwYKKwYBBAGsZgMKATCBwDApBggrBgEFBQcCARYdaHR0cDovL3d3dy5j ZXJ0LmZubXQuZXMvZHBjcy8wgZIGCCsGAQUFBwICMIGFDIGCQ2VydGlmaWNhZG8gcmVjb25vY2lk by4gU3VqZXRvIGEgbGFzIGNvbmRpY2lvbmVzIGRlIHVzbyBleHB1ZXN0YXMgZW4gbGEgRFBDIGRl IGxhIEZOTVQtUkNNIChDL0pvcmdlIEp1YW4gMTA2LTI4MDA5LU1hZHJpZC1Fc3Bhw7FhKTAlBggr BgEFBQcBAwQZMBcwCAYGBACORgEBMAsGBgQAjkYBAwIBDzCBtAYDVR0fBIGsMIGpMIGmoIGjoIGg hoGdbGRhcDovL2xkYXB1c3UuY2VydC5mbm10LmVzL2NuPUNSTDU0Mixjbj1BQyUyMEZOTVQlMjBV c3VhcmlvcyxvdT1DRVJFUyxvPUZOTVQtUkNNLGM9RVM/Y2VydGlmaWNhdGVSZXZvY2F0aW9uTGlz dDtiaW5hcnk/YmFzZT9vYmplY3RjbGFzcz1jUkxEaXN0cmlidXRpb25Qb2ludDANBgkqhkiG9w0B AQsFAAOCAQEAcnbW9VKe5c+pYVvhpKg0JBhMfkD/mwX459sIwh1gnGJ7r1GlVQmI5EKL0O+7ddw6 5sqFwHPevPW8CJX/+gwX0uPyBHTYs6weaDm/YCpJ62wk6r8xqKW2rkdF9M+vcLTkOt5galYVDJtz lrj9LVyb6AUSeeK2UggktwoCBIyRKpxh9okKUdHgi11bnqs9gTqACFBppCDS2tDMgCMaqwgfd5Ch LEHDFv8hiJBZ1ByT9/eNREx6kga6A3vWuTJUArMs/PX+F8Thr/6P376ouDxRjuYu/GODuxhcuUD7 864X/rzazRK4nbloj1DDaMglgO1g9+9GGIHa4TcOPc+wjoEnozGCAskwggLFAgEBMF8wSzELMAkG A1UEBhMCRVMxETAPBgNVBAoMCEZOTVQtUkNNMQ4wDAYDVQQLDAVDZXJlczEZMBcGA1UEAwwQQUMg Rk5NVCBVc3VhcmlvcwIQGIYyVbi4fLVXB2Zz7kKm5jAJBgUrDgMCGgUAoIIBPzAYBgkqhkiG9w0B CQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNjEwMDgwNzQ0NDJaMCMGCSqGSIb3DQEJ BDEWBBRbAc/lHCysqHwY1ofXIIeXzYCDvTBuBgkrBgEEAYI3EAQxYTBfMEsxCzAJBgNVBAYTAkVT MREwDwYDVQQKDAhGTk1ULVJDTTEOMAwGA1UECwwFQ2VyZXMxGTAXBgNVBAMMEEFDIEZOTVQgVXN1 YXJpb3MCEBiGMlW4uHy1Vwdmc+5CpuYwcAYLKoZIhvcNAQkQAgsxYaBfMEsxCzAJBgNVBAYTAkVT MREwDwYDVQQKDAhGTk1ULVJDTTEOMAwGA1UECwwFQ2VyZXMxGTAXBgNVBAMMEEFDIEZOTVQgVXN1 YXJpb3MCEBiGMlW4uHy1Vwdmc+5CpuYwDQYJKoZIhvcNAQEBBQAEggEAjxjBCBs17qzixVmayYAC 8gmQDd7zUzDawk7DgE3ugatsjy1Ro8/Xlz/PANiqXIzXSX2MuOys6AhYh4s4flKnj1aoIq+IOkX4 H3O72b9n6w0i1mVoVlHDfyjlgCfSzW/gerr+BBqF0QfNlKsuxsrOcw3iSDi7HgMD0IAdEWkDMzaX lPwqflbusXkKcA9yhzwzosHf6JmfOjX6I9tNH8SYskHj8wKHg4dXVt00x2T0Y7ITvlrQ0gmj/wqw D5ZJlIz1m8frfJDcGI0GiN2B+oRPd7/f0dY5BkdmS6dVC7pHw2rJVAYUIUwCzwGniQ02d4Crpz0x SVIGnPTbHnjbrbP/OQAAAAAAAA== --Apple-Mail=_E514AF6C-616B-4FE2-860A-2409F23DEF06--