Can anybody give me a mySQL query that would be the equivalent of the SQL Server query:

SELECT aa, NTILE(12) OVER (ORDER BY avgWksWork) AS work_nTile  FROM avgWk
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you exactly post your requirement, what do you want to do?
so, Im by no means a mysql expert, but NTILE in SQL Server just splits your result set into 12 segments or "tiles"....so, essentially, if you can assign a sequential increasing number to each record in your resultset, you can do something like this:

select ceiling(increasingnumber /12), * as tile
from yourtable

where the increasing number is divided by 12, and the result is your tile...  ceiling(1/12) = 1, 13/12 = 2, et al.

Does that make sense?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

quick change:

select ceiling(increasingnumber /12) as tile, *
from yourtable
studioEtcAuthor Commented:
ceiling(num/12) won't work: ex (1432/12) = 119.  I thought maybe  num mod 12, but it won't work either.

Here's what SQL Server gives me --to make a shorter sample I used NTILE(5) instead of NTILE(12).

SELECT emp, avgwkwk,  NTILE(5) OVER (ORDER BY avgwkwk) AS QUINTILE  FROM avgWk

ggg         2.46            1
ddd         8.75            1
fff          10.46            1
aaa       11.97            2
lll           12.46            2
hhh       19.24            2
ccc        32.56           3
iii           49.72           3
mmm    50.25           3
jjj           60.02           4
kkk        69.74           4
bbb       70.13           5
eee       80.21           5
studioEtcAuthor Commented:
I ended up using a loop, stepping thru each row of the table - yuck.  but at least it worked.  I ended up writing it in php, but it could have just as easily been a stored procedure.  Here the gist of the logic:

If your are doing nTile(5) and the table has 500 rows then it's pretty straight forward.  Order the table by the column that contains the metric you are ranking and each quintile will contain 100 rows.

The problem is when numRows mod 5 <> 0, as when your table has 503 rows.
503 mod 5 = 3 tells me that I have to disperse 3 extra rows.  - So the 1st, 2nd and 3rd quintiles will contain 101 rows and the 4th and 5th quintile will contain 100 rows. (it could also be 1st and 2nd quintile contain 100 rows and 3rd, 4th and 5th contain 101)

If you're working with nTile(24) and have 119 rows then the first 23 percentiles will contain 5 rows each and the 24th percentile will contain 4 (ie floor(119/24) )

Can anybody figure out how to do this just using just SQL without a while loop?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it is not possible in 1 single query, with mysql.

the logic is this:
if you have N rows (for example, N=100), and a "Tile" T = 5, meaning you want 5 tiles, you have to get N/T rows into each tile.
the problem is the rounding for example if you have N=98 and T=5, you want to have 3x20 and 2x19, and not 4x20 and 1x18

so, what you need is:
1) the row count from the table (applying any filters). must be done with a dedicated row count.
2) the unrounded number of rows per tile N/T. just the result of 1) + the value of T given.
3) a row number for each row. this part is possible, with a trick:

select @num := @num + 1 as row_number
, other_columns_go_here
 from your_table;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.