I'd like to show the observation number for each record returned by a PostgreSQL query.
I think in 8.4 windowing functions can perform this capability.
-
1I think I have to favorite my own question so I can come back to this in the future :)vol7ron– vol7ron2010-08-03 23:10:46 +00:00Commented Aug 3, 2010 at 23:10
-
33+1 This is the first question I've seen which consists entirely of questions, answers, and dialog from a single person.Xeoncross– Xeoncross2011-02-27 19:45:05 +00:00Commented Feb 27, 2011 at 19:45
-
2:) Xeon, you just made me laugh. I still come back to this question every once in a while.vol7ron– vol7ron2011-02-27 20:29:26 +00:00Commented Feb 27, 2011 at 20:29
Add a comment
|
2 Answers
select row_number() over (order by <field> nulls last) as rownum, *
from foo_tbl
order by <field>
If order is not necessary, this answer may also be simplified:
select row_number() over(), * -- notice: no fields are needed
from foo_tbl
SQL Fiddle Proof of Concept
6 Comments
rogerdpack
It appears that if you do
over() then it always gives rownumbers incrementally, like 1 2 3 4... in order of that particular result (if there are outer queries that rearrange results, then rownum could become out of order ref: stackoverflow.com/a/4812038/32453, so adding an order by might be useful in those cases (or if you want to not count nulls, as in the first example). FWIW.
Mysterious Otter
This is really cool -- for us newbies, could some dissect how this works?
vol7ron
@zthomas.nc its a window function. Think of a familiar glass window. If you wanted to, you could divide that window into smaller panes (frames), all the results are still uniquely there, but divided across the frames. This division is what is known as a partition, which is what the
over() does above. If you haven't supplied it any conditions, there'd be one pane for the entire window. Window functions are unique in that they can do calculations across the rows of a frame, instead of a whole result set. So if you wanted to do row_number by sex, you can use your over to partition by sex.
cdyson37
Seems to substantially slow down a large query
vol7ron
It shouldn’t have that much of an impact. Perhaps you’re hitting other limitations with the large query, like reduced working memory. If it is such a factor, and both row numbers and performance are necessary consider creating the numbers in your presentation or post-processing layer.
|
Postgres < 8.4
For versions prior to 8.4:
SELECT count(*) rownum, foo.*
FROM datatable foo
JOIN datatable bar
ON (foo.pk_id <= bar.pk_id)
GROUP BY foo.pk_id, foo.a, foo.b
ORDER BY rownum
;
-- if there isn't a single unique/primary key field, you can concatenate fields
-- Example: ON (foo.a||foo.b||foo.c <= bar.a||bar.b||bar.c)
Hope this helps someone.
SQL Fiddle Proof of Concept
Another Approach
I suggest avoiding this at all cost, but wanted to include it for posterity. It is somewhat expensive and I imagine does not scale well, but when a primary key does not exist on a table (bad db-design), your options may be limited. In most of those cases the recommendation is to perform numbering at the application layer.
-- Based on basic table w/o primary key
-- CREATE TABLE names ( name as text );
SELECT num, name[num]
FROM (
select generate_series( 1, (select count(*) from names) ) as num
) _nums,
(
select array_agg(name) as name from names
) _names
SQL Fiddle Proof of Concept
Reasons it doesn't scale:
- packing your tuples into an array for each row is a bad idea
5 Comments
vol7ron
This method should work on any SQL Standard compliant database
vol7ron
I think it's important to note that
null values should concatentate to null. Thus, a coalesce() may need to be used.
Arthur
This wont work, this is rank not row, consider 10,6,6,2 , rows 1,2,3,4,, but rank 1,3,3,4 with your query.
vol7ron
@Arthur: what is your primary key that's what pk stands for. A PK by definition must be unique unless it's a CK. Also, read the comment below the query; though there are conditions where it won't work (for example, I'm not sure you can concatenate null values)