Query returns more than one row for each subject-how to return ony top row after order is completed

Toad for Oracle & DevOps Toolkit
1

My query pulls state, county and tax rates and the effective date for each. This is by a column called geocode. Since changes are made to rates over the years, there can be multiple rows for each geocode.

I have successfully created my query to pull the correct data and to order it using the effective dates. What I’d like is to return only 1 row of data (after the data is ordered) for each geocode. The order by is geocode, state effective date, county effective date then city effective date. Here is a sample of the results of the query

101031500 19880202 0.06 19900201 0.01 0 0
101031500 19880202 0.06 19900201 0.01 0 0
101031500 19880202 0.06 0 0 0 0
101031500 19880202 0.06 0 0 0 0
101031500 0 0.05 19900201 0.01 0 0
101031500 0 0.05 19900201 0.01 0 0

040010140 20130701 0.065 19960701 0.01 20091001 0.035
040010140 20130701 0.065 19960701 0.01 0 0.025
040010140 20130701 0.065 0 0.005 20091001 0.035

The first column is the geocode, for each unique value, return the first row, so I want to see this as the results of my query

101031500 19880202 0.06 19900201 0.01 0 0

040010140 20130701 0.065 19960701 0.01 20091001 0.035

2

I can think of two ways to solve your problem. A windowing function, or moving your geolocation table up into the select.

Can you provide your SQL?

On Nov 7, 2013, at 10:12 AM, "lparncutt" bounce-lparncutt@toadworld.com wrote:

query returns more than one row for each subject-how to return ony top row after order is completed

Thread created by lparncutt
My query pulls state, county and tax rates and the effective date for each. This is by a column called geocode. Since changes are made to rates over the years, there can be multiple rows for each geocode.

I have successfully created my query to pull the correct data and to order it using the effective dates. What I'd like is to return only 1 row of data (after the data is ordered) for each geocode. The order by is geocode, state effective date, county effective date then city effective date. Here is a sample of the results of the query

101031500 19880202 0.06 19900201 0.01 0 0
101031500 19880202 0.06 19900201 0.01 0 0
101031500 19880202 0.06 0 0 0 0
101031500 19880202 0.06 0 0 0 0
101031500 0 0.05 19900201 0.01 0 0
101031500 0 0.05 19900201 0.01 0 0

040010140 20130701 0.065 19960701 0.01 20091001 0.035
040010140 20130701 0.065 19960701 0.01 0 0.025
040010140 20130701 0.065 0 0.005 20091001 0.035

The first column is the geocode, for each unique value, return the first row, so I want to see this as the results of my query

101031500 19880202 0.06 19900201 0.01 0 0

040010140 20130701 0.065 19960701 0.01 20091001 0.035

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Oracle notifications altogether.

Toad World - Oracle Discussion Forum

Flag this post as spam/abuse.

3

select

concat(concat(lpad(st.geostate,2,0), lpad(co.geocounty,3,0)), lpad(ci.geocity,4,0)) GEOCODE,

st.strateeffdate1 state_rate_eff_date, (case when st.strate1=-1 then 0 else st.strate1 end) state_rate,

co.corateeffdate1 county_rate_eff_date, (case when co.corate1=-1 then 0 else co.corate1 end) county_rate,

ci.cirateeffdate1 city_rate_eff_date, (case when ci.cirate1=-1 then 0 else ci.cirate1 end) city_rate

from

vertex.ratestate st,

vertex.ratecounty co,

vertex.ratecity ci

where ST.GEOSTATE = CO.GEOSTATE

and CO.GEOCOUNTY = CI.GEOCOUNTY

and ST.GEOSTATE = CI.GEOSTATE

and st.strateeffdate1 = (select Max(st.strateeffdate1) from vertex.ratestate)

and co.corateeffdate1 = (select Max(co.corateeffdate1) from vertex.ratecounty)

and ci.cirateeffdate1 = (select Max(ci.cirateeffdate1) from vertex.ratecity)

and st.geostate in (04,10) --04 --10 --47

and co.geocounty in (001,103)–001 --103 --760

and ci.geocity in (0140,1500)–0140 --1500 --0550

order by geocode, st.strateeffdate1 DESC, co.corateeffdate1 DESC, ci.cirateeffdate1 DESC