Member Avatar for kgizo

I have Post code column and I want to get 60, 30 and 10% for each area code that I have, but the thing is there is a between statement which makes things a bit complicated. I need 60% from the first postcode then 30% from the second and 10% from the last postcode i.e these are the people I am trying to pull from each postcode. Here is my code below:

SELECT top 50000 
     id
    ,email 
FROM fn_getsacilist ('knowblet,saciprofiled,govdata,tobydata',25000000,10,0)
WHERE idnumber>='66' 
    and idnumber<='98'
    and (postcode between 2000 and 2199
            or postcode between 4000 and 4099
            or postcode between 7400 and 8099)
    and income>=15000

Now to get results of a specific province then I would pull the data according to the postcodes like this postcode between 0001 and 2199 for instance.

But the request in my particular case is to pull the data in this manner:

JHB-60% // Cape Town-30% // Durban-10% and the only way to do this is by pulling the data by postcode as in my code above.

  • 2 Contributors
  • 3 Replies
  • 273 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

asked for a 60/30/10 split across three postcode ranges (JHB / CT / DBN) and suggested using TOP and iterative checks. A clearer, repeatable pattern is to (1) decide the overall sample size, (2) calculate absolute targets for each area (rounding as needed), then (3) pull each area's target with a deterministic or randomized ordering and combine the results. This avoids trial-and-error and keeps the math explicit.

Example T-SQL pattern (compute targets, classify rows, rank within each area, then pick the top N per area):

DECLARE @Total INT = 50000;
DECLARE @JHB INT   = CEILING(@Total * 0.60);
DECLARE @CT INT    = CEILING(@Total * 0.30);
DECLARE @DBN INT   = @Total - @JHB - @CT;

WITH Filtered AS (
  SELECT id, email,
    CASE
      WHEN postcode BETWEEN 2000 AND 2199 THEN 'JHB'
      WHEN postcode BETWEEN 7400 AND 8099 THEN 'CT'
      WHEN postcode BETWEEN 4000 AND 4099 THEN 'DBN'
    END AS area
  FROM fn_getsacilist('knowblet,saciprofiled,govdata,tobydata',25000000,10,0)
  WHERE idnumber BETWEEN '66' AND '98' AND income >= 15000
),
Ranked AS (
  SELECT id, email, area,
    ROW_NUMBER() OVER (PARTITION BY area ORDER BY NEWID()) AS rn
  FROM Filtered
  WHERE area IS NOT NULL
)
SELECT id, email
FROM Ranked
WHERE (area = 'JHB' AND rn <= @JHB)
   OR (area = 'CT'  AND rn <= @CT)
   OR (area = 'DBN' AND rn <= @DBN);

Notes and cautions:

  • If any area lacks enough rows to meet its target, the final set will be smaller; either reduce the overall total or redistribute the shortfall (compute available counts first and cap targets with MIN()).
  • ORDER BY NEWID() randomizes selection but is expensive on large sets. For large tables consider a stable hash approach (e.g., ABS(CHECKSUM(id))) to avoid sorting the entire set, or pre-filter and index by postcode before randomizing.
  • Confirm postcode→city mappings and deduplication rules (emails may appear in multiple source tables from fn_getsacilist). Indexes on postcode and id dramatically improve performance when applying these filters.

This pattern makes the 60/30/10 intent explicit, ties back to 's TOP idea but avoids manual trial-and-error, and gives clear places to add redistribution or performance tweaks.

Recommended Answers

All 3 Replies

Member Avatar for rproffitt

The specification here is unclear but if I were to guess you want groups that correspond to 10% of the total by zip/postal code.

I don't see how I could get that 10% (or other numbers) without some trial and error. That is, you know the total count so let's say that's 1 million entries. To get 10% or 100,000 entries from a zip/postal code range is going to take us trying SQL queries until we get our 100K count result.

Now what if you wanted to change that spec to be "I want to randomly send something to 10%"? That's another question altogether.

For now, since there is only this one case, you should bruteforce the query as it's just this one case.

Member Avatar for kgizo

Yes rproffitt thats exactly what I want, groups that correspond to 10% of the total by zip/postal code.

Member Avatar for rproffitt

Sorry I had a memory lapse here. First read http://www.w3schools.com/sql/sql_top.asp

Aha, you can get the top 10 percent from a query. For your idea sort by zip/postal code.
But the next problem is how to get the other records.

I'm think maybe we have to get the row numbers from 3 top queries. The the record numbers will be used like we read at http://stackoverflow.com/questions/7332731/sql-server-select-top-10-20-results

Sorry if I don't write this for you but the next day and another run at it seems to find it may be doable. For me I'd be doing this in code since that's how I use MySQL. Be sure to check how TOP works in your version of MySQL.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.