CODEWITHSUNDEEP
mysqlsql
Nathan Power
Nathan Power

Reputation: 650

MySQL query to assign values to a field based in an iterative manner

I am using a MySql table with 500,000 records. The table contains a field (abbrevName) which stores a two-character representation of the first two letters on another field, name.

For example AA AB AC and so on.

What I want to achieve is the set the value of another field (pgNo) which stores a value for page number, based on the value of that records abbrevName.

So a record with an abbrevName of 'AA' might get a page number of 1, 'AB' might get a page number of 2, and so on.

The catch is that although multiple records may have the same page number (after all multiple entities might have a name beginning with 'AA'), once the amount of records with the same page number reaches 250, the page number must increment by one. So after 250 'AA' records with a page number of 1, we must assign futher 'AA records with a page number of 2, and so on.

My Pseudocode looks something like this:

-Count distinct abbrevNames
-Count distinct abbrevNames with more than 250 records
-For the above abbrevNames count the the sum of each divided by 250
-Output a temporary table sorted by abbrevName
-Use the total number of distinct page numbers with 250 or less records to assign page numbers incrementally

I am really struggling to put anything together in a query that comes close to this, can anyone help with my logic or some code ?

Upvotes: 2

Views: 369

Answers (1)

fancyPants
fancyPants

Reputation: 51868

Please have a try with this one:

SELECT abbrevNames, CAST(pagenumber AS signed) as pagenumber FROM (
    SELECT
    abbrevNames
    , IF(@prev = abbrevNames, @rows_per_abbrev:=@rows_per_abbrev + 1, @pagenr:=@pagenr + 1)
    , @prev:=abbrevNames
    , IF(@rows_per_abbrev % 250 = 0, @pagenr:=@pagenr + 1, @pagenr) AS pagenumber
    , IF(@rows_per_abbrev % 250 = 0, @rows_per_abbrev := 1, @rows_per_abbrev)
    FROM
    yourTable
    , (SELECT @pagenr:=0, @prev:=NULL, @rows_per_abbrev:=0) variables_initialization
    ORDER BY abbrevNames
) subquery_alias

UPDATE: I had misunderstood the question a bit. Now it should work

Upvotes: 2

Related Questions