Обсуждение: query based on row number for psql8.3

Hello,

For psql8.3, is there a simple way to query based on row number please?

For example,

select  case                    when row_num=1 THEN    t1.tot_hr                    ELSE
 t1.tot_hr - 
 
t1.pre_hr          END
FROM  t1 ;

On 03/23/2016 11:00 AM, Emi wrote:
> Hello,
>
> For psql8.3, is there a simple way to query based on row number please?

First FYI, 8.3 is 3 years past its end of life.

Second, Postgres does not have a built in row_num. Anything that could 
be cobbled together would be dependent on the ordering specified.

So what exactly are you trying to do?
In other words what is row_num supposed to represent?

>
> For example,
>
> select  case
>                      when row_num=1 THEN    t1.tot_hr
>                      ELSE                                  t1.tot_hr -
> t1.pre_hr
>            END
> FROM  t1 ;
>
>
>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Row_number is an "analytic" function, used in conjunction with the "partition by"
Ror_number is a logical concept so depends on the "order by" component of the "partition by" clause

You need to wrap your query with row_number in an outer query and apply thye case statement to the outer query.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi
Sent: Wednesday, March 23, 2016 2:01 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] query based on row number for psql8.3

Hello,

For psql8.3, is there a simple way to query based on row number please?

For example,

select  case                    when row_num=1 THEN    t1.tot_hr                    ELSE
 t1.tot_hr -
 
t1.pre_hr          END
FROM  t1 ;





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY
CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the
intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this
communicationin error, please delete the message without making any copies and notify the sender so that we may correct
ourrecords. Thank you.
 

A "goggle" of row_number()  function for postgresql seems to indicate its only available in 8.4 and above :-(

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Stephen Tahmosh
Sent: Wednesday, March 23, 2016 2:10 PM
To: emilu@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] query based on row number for psql8.3

Row_number is an "analytic" function, used in conjunction with the "partition by"
Ror_number is a logical concept so depends on the "order by" component of the "partition by" clause

You need to wrap your query with row_number in an outer query and apply thye case statement to the outer query.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi
Sent: Wednesday, March 23, 2016 2:01 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] query based on row number for psql8.3

Hello,

For psql8.3, is there a simple way to query based on row number please?

For example,

select  case                    when row_num=1 THEN    t1.tot_hr                    ELSE
 t1.tot_hr -
 
t1.pre_hr          END
FROM  t1 ;





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY
CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the
intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this
communicationin error, please delete the message without making any copies and notify the sender so that we may correct
ourrecords. Thank you.
 

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY
CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the
intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this
communicationin error, please delete the message without making any copies and notify the sender so that we may correct
ourrecords. Thank you.
 

On Wednesday, March 23, 2016, Stephen Tahmosh <stahmosh@shieldsrx.com> wrote:

Row_number is an "analytic" function, 

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.

On 03/23/2016 11:15 AM, Stephen Tahmosh wrote:
> Hi,
>
> Excuse my ignorance.
>
> What is a “top-post”?

https://en.wikipedia.org/wiki/Posting_style#Top-posting

>
> Thanks,
>
> STeve
>



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 03/23/2016 12:00 PM, Emi wrote:
> Hello,
>
> For psql8.3, is there a simple way to query based on row number please?
>
> For example,
>
> select  case
>                     when row_num=1 THEN    t1.tot_hr
>                     ELSE t1.tot_hr - t1.pre_hr
>           END
> FROM  t1 ;
>
>
>
>
>
I seem to recall other posts from condordia:  Someone needs to take of 
their skates and update postgres!

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Rob Sargent
Sent: Wednesday, March 23, 2016 2:27 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] query based on row number for psql8.3



On 03/23/2016 12:00 PM, Emi wrote:
> Hello,
>
> For psql8.3, is there a simple way to query based on row number please?
>
> For example,
>
> select  case
>                     when row_num=1 THEN    t1.tot_hr
>                     ELSE t1.tot_hr - t1.pre_hr
>           END
> FROM  t1 ;
>
>
>
>
>
I seem to recall other posts from condordia:  Someone needs to take of
their skates and update postgres!

[ST]
I'm not  sure if this link works, as I don't have access to 8.3
http://postgresql.nabble.com/How-to-realize-ROW-NUMBER-in-8-3-td4328448.html

If this works, then wrap the query with a main query that uses "rownum" in the case statement.

STeve



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY
CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the
intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this
communicationin error, please delete the message without making any copies and notify the sender so that we may correct
ourrecords. Thank you.