Thread: Compare rows
Hi,
I have the following table.
city Rate flag
A 10 0
A 20 1
A 30 0
A 2 0
A 23 1
A 12 0
B 5 1
B 43 0
C 23 1
C 67 1
For every city I have so set the flag as -1 in row which is 0 above the row having flag as 1.
So my out would be:
city Rate flag
A 10 -1
A 20 1
A 30 0
A 2 -1
A 23 1 (not changed to -1 as it not 0)
A 12 0 (not changed to -1 because i need to compare it to row of same city. so being last row not compared with any row)
----------------------------------------------------
B 5 1
B 43
C 23 1
C 67 1
On 13 July 2011 07:15, LALIT KUMAR <lalit.jss@gmail.com> wrote: > Hi, > I have the following table. > city Rate flag > A 10 0 > A 20 1 > A 30 0 > A 2 0 > A 23 1 > A 12 0 > B 5 1 > B 43 0 > C 23 1 > C 67 1 > For every city I have so set the flag as -1 in row which is 0 above the row > having flag as 1. > So my out would be: > city Rate flag > A 10 -1 > A 20 1 > A 30 0 > A 2 -1 > A 23 1 (not changed to -1 as it not 0) > A 12 0 (not changed to -1 because i need to compare > it to row of same city. so being last row not compared with any row) > ---------------------------------------------------- > B 5 1 > B 43 > C 23 1 > C 67 1 There doesn't appear to be any particular order to your rows. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> Hi, > I have the following table. > city Rate flag > A 10 0 > A 20 1 > A 30 0 > A 2 0 > A 23 1 > A 12 0 > B 5 1 > B 43 0 > C 23 1 > C 67 1 > For every city I have so set the flag as -1 in row which is 0 above the > row > having flag as 1. > So my out would be: > city Rate flag > A 10 -1 > A 20 1 > A 30 0 > A 2 -1 > A 23 1 (not changed to -1 as it not 0) > A 12 0 (not changed to -1 because i need to compare > it to row of same city. so being last row not compared with any row) > ---------------------------------------------------- > B 5 1 > B 43 > C 23 1 > C 67 1 There doesn't appear to be any particular order to your rows. * Yeah, I can't figure out the order either. Why does A2 appear after A30? And A 23 above A 12? Which column(s) do you order your records by? Does your table have more fields beside the three shown? Some PK you're using as order key...? Best, Oliver -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
On 13 July 2011 07:15, LALIT KUMAR <lalit.jss@gmail.com> wrote:
> Hi,
> I have the following table.
> city Rate flag
> A 10 0
> A 20 1
> A 30 0
> A 2 0
> A 23 1
> A 12 0
> B 5 1
> B 43 0
> C 23 1
> C 67 1
> For every city I have so set the flag as -1 in row which is 0 above the row
> having flag as 1.
> So my out would be:
> city Rate flag
> A 10 -1
> A 20 1
> A 30 0
> A 2 -1
> A 23 1 (not changed to -1 as it not 0)
> A 12 0 (not changed to -1 because i need to compare
> it to row of same city. so being last row not compared with any row)
> ----------------------------------------------------
> B 5 1
> B 43
> C 23 1
> C 67 1
You could try this:
UPDATE
my_table
SET
flag = -1
FROM (
SELECT
city, dat, lead(flag, 1, 0) OVER (PARTITION BY city ORDER BY
dat) AS next_flag
FROM
my_table
) t2
WHERE
my_table.city = t2.city
AND
my_table.dat = t2.dat
AND
flag = 0
AND
next_flag = 1
This joins the target table with a subquery that uses a window
function to work out what the next value of the date column is, and
updates based on that.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company