Ranking Variable

Hi

I would like to know if it's possible to make a ranking variable in a query which numbers the rows from 1, 2, 3, 4, 5.....n consecutively. I need to rank the results I receive for a particular resultset, which are displayed in descending order.

tx in advance

Do you mean just displaying 1 2 3... beside each record? If so this is normally done programmatically not in a query though I think it can be done I just haven't done it before. Can you clear up what you're trying to get.

you could possibly define a SEQUENCE that would keep track of your last ranking.

Or do you want to do it all at once, just make the "ranked" table/view and be done with it?

I had some inkling from somewhere that primary keys create a sequence automagically, and thus when you insert this field is generated??? sorry I don't have time to try it today...

or, you can fill a table with the results of a query with CREATE TABLE AS and just select from a table that already has sequential numbers in it, or write a procedure that would use the sequence from before as its starting point, and run a trigger on INSERT into the table that you were originally ranking. It depends on when you are assigning the ranking in your overall problem.

i've done it with pure sql before, it is a very inefficient query but it can be done

still, you should consider doing it in your application program as Viper_SB suggested

Hi

Thanx for the suggestions!

I know I cannot use the primary key cos the order in which I get the resultset will constantly change. And yes I am basically numbering the rows in the order that they are retrieved.
As the data will be ordered from highest to lowest premium amounts.

I would like to know if there is any documentation available in order to do this programatically, and whether it would negatively affect the performance of the query?

Tx

Doing it programatically shouldn't affect the performance and in my opinon I think would help it. Which language are you coding in?

I am using JAVA and XML to display the reports. And using postgres psql to write functions

I don't use Java but I'd imagen it's something similar to (syntax maybe off):



or if you use a for loop it's even easier




something like that would be the easiest way to go, you might want to ask in the java forum for the correct syntax

tx viper, will try it out...and let you know