agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedquery based on row number for psql8.3
10+ messages / 6 participants
[nested] [flat]
* query based on row number for psql8.3
@ 2016-03-23 18:00 Emi <emilu@encs.concordia.ca>
2016-03-23 18:07 ` Re: query based on row number for psql8.3 Adrian Klaver <adrian.klaver@aklaver.com>
2016-03-23 18:09 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:26 ` Re: query based on row number for psql8.3 Rob Sargent <robjsargent@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Emi @ 2016-03-23 18:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
@ 2016-03-23 18:07 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2016-03-23 18:07 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
@ 2016-03-23 18:09 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:13 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:14 ` Re: query based on row number for psql8.3 David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 2 replies; 10+ messages in thread
From: Stephen Tahmosh @ 2016-03-23 18:09 UTC (permalink / raw)
To: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
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 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:09 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
@ 2016-03-23 18:13 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephen Tahmosh @ 2016-03-23 18:13 UTC (permalink / raw)
To: Stephen Tahmosh <stahmosh@shieldsrx.com>; emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
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 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.
--
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 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:09 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
@ 2016-03-23 18:14 ` David G. Johnston <david.g.johnston@gmail.com>
2016-03-23 18:15 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
1 sibling, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2016-03-23 18:14 UTC (permalink / raw)
To: Stephen Tahmosh <stahmosh@shieldsrx.com>; +Cc: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
On Wednesday, March 23, 2016, Stephen Tahmosh <stahmosh@shieldsrx.com>
wrote:
> Row_number is an "analytic" function,
Row_number is a window function and those did not exist back in 8.3...
And please don't top-post.
Thanks!
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:09 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:14 ` Re: query based on row number for psql8.3 David G. Johnston <david.g.johnston@gmail.com>
@ 2016-03-23 18:15 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:17 ` Re: query based on row number for psql8.3 Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 10+ messages in thread
From: Stephen Tahmosh @ 2016-03-23 18:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
Hi,
Excuse my ignorance.
What is a “top-post”?
Thanks,
STeve
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Wednesday, March 23, 2016 2:15 PM
To: Stephen Tahmosh <stahmosh@shieldsrx.com>
Cc: emilu@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] query based on row number for psql8.3
On Wednesday, March 23, 2016, Stephen Tahmosh <stahmosh@shieldsrx.com<mailto:stahmosh@shieldsrx.com>> wrote:
Row_number is an "analytic" function,
Row_number is a window function and those did not exist back in 8.3...
And please don't top-post.
Thanks!
David J.
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:09 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:14 ` Re: query based on row number for psql8.3 David G. Johnston <david.g.johnston@gmail.com>
2016-03-23 18:15 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
@ 2016-03-23 18:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2016-03-23 18:17 UTC (permalink / raw)
To: Stephen Tahmosh <stahmosh@shieldsrx.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
@ 2016-03-23 18:26 ` Rob Sargent <robjsargent@gmail.com>
2016-03-23 18:57 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
2 siblings, 1 reply; 10+ messages in thread
From: Rob Sargent @ 2016-03-23 18:26 UTC (permalink / raw)
To: pgsql-sql
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!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:26 ` Re: query based on row number for psql8.3 Rob Sargent <robjsargent@gmail.com>
@ 2016-03-23 18:57 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 20:34 ` Re: query based on row number for psql8.3 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Stephen Tahmosh @ 2016-03-23 18:57 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
-----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 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: query based on row number for psql8.3
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:26 ` Re: query based on row number for psql8.3 Rob Sargent <robjsargent@gmail.com>
2016-03-23 18:57 ` Re: query based on row number for psql8.3 Stephen Tahmosh <stahmosh@shieldsrx.com>
@ 2016-03-23 20:34 ` Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Michael Moore @ 2016-03-23 20:34 UTC (permalink / raw)
To: Stephen Tahmosh <stahmosh@shieldsrx.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql
It was hinted at in previous responses, but if you do:
Select * from t1;
and then again
select * from t1;
You have no guarantee that the results will be in the same order. So, even
if your query DID work, it would be meaningless because you should*
consider* the records to come in a random order. Never assume the order,
always specify it if it is important.
Mike
On Wed, Mar 23, 2016 at 11:57 AM, Stephen Tahmosh <stahmosh@shieldsrx.com>
wrote:
>
>
> -----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 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.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2016-03-23 20:34 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-23 18:00 query based on row number for psql8.3 Emi <emilu@encs.concordia.ca>
2016-03-23 18:07 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-03-23 18:09 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:13 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:14 ` David G. Johnston <david.g.johnston@gmail.com>
2016-03-23 18:15 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 18:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-03-23 18:26 ` Rob Sargent <robjsargent@gmail.com>
2016-03-23 18:57 ` Stephen Tahmosh <stahmosh@shieldsrx.com>
2016-03-23 20:34 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox