using ROWNUM to update distinct streets 2

[oneleaf5]

Hi Everyone,

I'm trying to flag only the first record for a street that has a positive latitude value:

update savoy
set flag = 'Y'
where street =
(select distinct street
from savoy
where lat <> 0
and rownum = 1);


The result I would like is this:

STREET LAT LON FLAG
miller st 0.000000 0.000000
miller st 0.000000 0.000000
miller st 41.951319 -70.90576 Y
miller st 41.951400 -70.90580
miller st 41.951425 -70.90640
rabbit ave 0.000000 0.000000
rabbit ave 0.000000 0.000000
orange ave 41.857287 -71.55670 Y
orange ave 41.864087 -71.58000
orange ave 41.867057 -71.58794
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000


Instead, I'm getting this:

STREET LAT LON FLAG
miller st 0.000000 0.000000
miller st 0.000000 0.000000
miller st 41.951319 -70.90576 Y
miller st 41.951400 -70.90580 Y
miller st 41.951425 -70.90640 Y
rabbit ave 0.000000 0.000000
rabbit ave 0.000000 0.000000
orange ave 41.857287 -71.55670 Y
orange ave 41.864087 -71.58000 Y
orange ave 41.867057 -71.58794 Y
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000
guerney dr 0.000000 0.000000


The latter result flags every instance of orange ave that has a positive LAT -- instead, I only want the first record.

Can anyone tell me what I'm doing wrong?

Many thanks,

oneleaf

 

[carp]

Look at the output that you get from your subquery. It is going to give you 'orange'. Now your update statement is going to update every record where street = 'orange'.

If your table has a primary key (please, tell me your table has a primary key!), then you could probably factor that into your statement to your advantage. Something along the lines of

update savoy
set flag = 'Y'
where (street, primary_key_column) IN  
(select street, min(primary_key_column)
from savoy
where lat <> 0
group by street);

I haven't tested this, but it should at least be close.

 

[Dagon]

If you haven't got a primary key column or don't know what it is, you can use ROWID instead:

update savoy s1
set flag = 'Y'
where rowid = 
(select min(rowid)
from savoy s2
where s2.street = s1.street
and lat <> 0)
and lat <> 0

 

[carp]

As a side note, you also need to define what you mean by "first". Over time, a given row's location can change, "physical first" will differ. Also, the way you structure a query or Oracle pulls the data into the buffer cache can change the logical meaning of "first". So if you have some specific criteria (such as the lowest primary key/rowid value, earliest creation date, etc) you will need to factor that into your final statement.

 

[oneleaf5]

carp --

Your solution worked perfectly. Thanks very much. Thanks, too, for the side note. Right now, I don't care about which lat/lon pair gets chosen for the street, just as long as the street gets represented.

cheers,

oneleaf

 

[Thargy]

Oneleaf,

on your behalf I have done the appropriate thing and awarded Carp a star. Was there some reason for you not doing so?

Regards

T

 

[LarrySteele]

Carp - perfect. I had a similar situation. Googled and went through some of my books to no avail. Searched this forum and bam! There's the solution.

Have another star - you helped me finish a project early!

 

[carp]

Reusable answers - almost as good as reusable code! Glad it worked for you, Larry.