(PostgreSQL) actual record number in recordset
Ffor testing purposes I need to write a SQL query which contains the
actual record number as a column in the result set. If my SELECT gets
back to me with 10 records as the result, I need to have one column
which contains the values 1-10.
Is there a way to achieve this without a stored procedure cursoring through
my data?
I need this on PostgreSQL.
sql postgresql
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
add a comment |
Ffor testing purposes I need to write a SQL query which contains the
actual record number as a column in the result set. If my SELECT gets
back to me with 10 records as the result, I need to have one column
which contains the values 1-10.
Is there a way to achieve this without a stored procedure cursoring through
my data?
I need this on PostgreSQL.
sql postgresql
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
1
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04
add a comment |
Ffor testing purposes I need to write a SQL query which contains the
actual record number as a column in the result set. If my SELECT gets
back to me with 10 records as the result, I need to have one column
which contains the values 1-10.
Is there a way to achieve this without a stored procedure cursoring through
my data?
I need this on PostgreSQL.
sql postgresql
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
Ffor testing purposes I need to write a SQL query which contains the
actual record number as a column in the result set. If my SELECT gets
back to me with 10 records as the result, I need to have one column
which contains the values 1-10.
Is there a way to achieve this without a stored procedure cursoring through
my data?
I need this on PostgreSQL.
sql postgresql
sql postgresql
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
edited Nov 16 '18 at 19:22
John Saunders
147k22204364
147k22204364
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
asked Sep 9 '09 at 14:26
KB22KB22
4,78173752
4,78173752
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
1
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04
add a comment |
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
1
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
1
1
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04
add a comment |
3 Answers
3
active
oldest
votes
You could partition your data and get a row_number()
For example:
SELECT FirstName, LastName, SalesYTD, PostalCode,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
See the following:
ROW_NUMBER (Transact-SQL)
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
add a comment |
Have a look at ROW_NUMBER() (SQL Server 2005 and above)
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
add a comment |
If you're on 8.4, you can use window functions (row_number() to be exact).
If you're on pre 8.4, you can use the technique I described some time ago on my blog.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1400056%2fpostgresql-actual-record-number-in-recordset%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could partition your data and get a row_number()
For example:
SELECT FirstName, LastName, SalesYTD, PostalCode,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
See the following:
ROW_NUMBER (Transact-SQL)
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
add a comment |
You could partition your data and get a row_number()
For example:
SELECT FirstName, LastName, SalesYTD, PostalCode,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
See the following:
ROW_NUMBER (Transact-SQL)
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
add a comment |
You could partition your data and get a row_number()
For example:
SELECT FirstName, LastName, SalesYTD, PostalCode,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
See the following:
ROW_NUMBER (Transact-SQL)
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
You could partition your data and get a row_number()
For example:
SELECT FirstName, LastName, SalesYTD, PostalCode,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
See the following:
ROW_NUMBER (Transact-SQL)
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
edited Sep 9 '09 at 14:33
John Saunders
147k22204364
147k22204364
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
answered Sep 9 '09 at 14:29
Jason IrwinJason Irwin
1,26922241
1,26922241
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
add a comment |
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard.
– J. Polfer
Sep 9 '09 at 14:34
add a comment |
Have a look at ROW_NUMBER() (SQL Server 2005 and above)
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
add a comment |
Have a look at ROW_NUMBER() (SQL Server 2005 and above)
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
add a comment |
Have a look at ROW_NUMBER() (SQL Server 2005 and above)
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
Have a look at ROW_NUMBER() (SQL Server 2005 and above)
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
answered Sep 9 '09 at 14:28
Cade RouxCade Roux
73k36152250
73k36152250
add a comment |
add a comment |
If you're on 8.4, you can use window functions (row_number() to be exact).
If you're on pre 8.4, you can use the technique I described some time ago on my blog.
add a comment |
If you're on 8.4, you can use window functions (row_number() to be exact).
If you're on pre 8.4, you can use the technique I described some time ago on my blog.
add a comment |
If you're on 8.4, you can use window functions (row_number() to be exact).
If you're on pre 8.4, you can use the technique I described some time ago on my blog.
If you're on 8.4, you can use window functions (row_number() to be exact).
If you're on pre 8.4, you can use the technique I described some time ago on my blog.
answered Sep 9 '09 at 16:40
user80168
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f1400056%2fpostgresql-actual-record-number-in-recordset%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4
– Jason Irwin
Sep 9 '09 at 14:35
1
@cœur how's that?
– John Saunders
Nov 16 '18 at 19:22
@JohnSaunders even better, thanks :)
– Cœur
Nov 18 '18 at 2:04