Ufdjrw

Are British MPs missing the point, with these 'Indicative Votes'?

Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?

In the UK, is it possible to get a referendum by a court decision?

What is a Samsaran Word™?

ssTTsSTtRrriinInnnnNNNIiinngg

Where would I need my direct neural interface to be implanted?

Am I breaking OOP practice with this architecture?

Why is the sentence "Das ist eine Nase" correct?

Mathematica command that allows it to read my intentions

Finding the reason behind the value of the integral.

Is it possible to map the firing of neurons in the human brain so as to stimulate artificial memories in someone else?

Bullying boss launched a smear campaign and made me unemployable

Why was Sir Cadogan fired?

How seriously should I take size and weight limits of hand luggage?

Why were 5.25" floppy drives cheaper than 8"?

Placement of More Information/Help Icon button for Radio Buttons

Should I tell management that I intend to leave due to bad software development practices?

How to compactly explain secondary and tertiary characters without resorting to stereotypes?

What are the G forces leaving Earth orbit?

Can someone clarify Hamming's notion of important problems in relation to modern academia?

How to show a landlord what we have in savings?

Implication of namely

Convert seconds to minutes

How exploitable/balanced is this homebrew spell: Spell Permanency?

MySQL query the same amount of states in a set amount of records returned

How do you set a default value for a MySQL Datetime column?How to output MySQL query results in CSV format?How do I limit the number of rows returned by an Oracle query after ordering?MySQL Query GROUP BY day / month / yearFind duplicate records in MySQLRetrieving the last record in each group - MySQLmysql php query HAVING clausegroup by having clause querymysql - find records where conditions are met in other tableHow can i set different limit different columns in one query in SQL, Codeigniter?

0

If I have a phone column and a state column. Can i query a specific LIMIT of lets say 500 but I want every state thats available in the state column to have an equal amount of each in the 500 count query.

ex: table = ironman

appid
phone(PK)
firstname
lastname
address
city
state
zip
called_count

Query:

SELECT 
FROM ironman
(A scripts that gives me (x amount) of phone numbers from each state)
WHERE called_count <= 4
LIMIT 500 OFFSET 0;

mysql sql

share|improve this question

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which MySQL version? use SELECT VERSION() .. I assume you want a LIMIT per state group? See Why should I provide an MCVE for what seems to me to be a very simple SQL query? for providng example data and expected results on a smaller scale then 10.000 records.
  
  – Raymond Nijland
  Mar 7 at 21:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  using version 8.0.15
  
  – Rakonu
  Mar 7 at 21:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  redid the question a bit to better understand the table
  
  – Rakonu
  Mar 7 at 21:48
  

  
  
  
  

add a comment | 

0

If I have a phone column and a state column. Can i query a specific LIMIT of lets say 500 but I want every state thats available in the state column to have an equal amount of each in the 500 count query.

ex: table = ironman

appid
phone(PK)
firstname
lastname
address
city
state
zip
called_count

Query:

SELECT 
FROM ironman
(A scripts that gives me (x amount) of phone numbers from each state)
WHERE called_count <= 4
LIMIT 500 OFFSET 0;

mysql sql

share|improve this question

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which MySQL version? use SELECT VERSION() .. I assume you want a LIMIT per state group? See Why should I provide an MCVE for what seems to me to be a very simple SQL query? for providng example data and expected results on a smaller scale then 10.000 records.
  
  – Raymond Nijland
  Mar 7 at 21:25
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  using version 8.0.15
  
  – Rakonu
  Mar 7 at 21:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  redid the question a bit to better understand the table
  
  – Rakonu
  Mar 7 at 21:48
  

  
  
  
  

add a comment | 

If I have a phone column and a state column. Can i query a specific LIMIT of lets say 500 but I want every state thats available in the state column to have an equal amount of each in the 500 count query.

ex: table = ironman

appid
phone(PK)
firstname
lastname
address
city
state
zip
called_count

Query:

SELECT 
FROM ironman
(A scripts that gives me (x amount) of phone numbers from each state)
WHERE called_count <= 4
LIMIT 500 OFFSET 0;

mysql sql

share|improve this question

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

appid
phone(PK)
firstname
lastname
address
city
state
zip
called_count

SELECT 
FROM ironman
(A scripts that gives me (x amount) of phone numbers from each state)
WHERE called_count <= 4
LIMIT 500 OFFSET 0;

share|improve this question

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

share|improve this question

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

edited Mar 8 at 1:50

GMB

20.8k51028

edited Mar 8 at 1:50

GMB

20.8k51028

asked Mar 7 at 21:17

RakonuRakonu

102

asked Mar 7 at 21:17

RakonuRakonu

102

1 Answer
1

active

oldest

votes

0

Use ROW_NUMBER() to assign the rows a position within a state group. Then order the rows by that postion. When you then apply a LIMIT, there will be the same number of rows from each state (+-1) - (assuming you have enough phone numbers for each state).

with numbered as (
 select i.*
 , row_number() over (partition by state order by phone) pos
 from ironman i
 -- put your WHERE clause here
), limited as (
 select *
 from numbered
 order by pos asc, state asc
 limit 6 -- set your limit here
)
select *
from limited
order by state, phone

db-fiddle

If you want the phone numbers to be selected randomly - change the ORDER for position

row_number() over (partition by state order by rand()) pos

share|improve this answer

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55052938%2fmysql-query-the-same-amount-of-states-in-a-set-amount-of-records-returned%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Use ROW_NUMBER() to assign the rows a position within a state group. Then order the rows by that postion. When you then apply a LIMIT, there will be the same number of rows from each state (+-1) - (assuming you have enough phone numbers for each state).

with numbered as (
 select i.*
 , row_number() over (partition by state order by phone) pos
 from ironman i
 -- put your WHERE clause here
), limited as (
 select *
 from numbered
 order by pos asc, state asc
 limit 6 -- set your limit here
)
select *
from limited
order by state, phone

db-fiddle

If you want the phone numbers to be selected randomly - change the ORDER for position

row_number() over (partition by state order by rand()) pos

share|improve this answer

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

add a comment | 

0

Use ROW_NUMBER() to assign the rows a position within a state group. Then order the rows by that postion. When you then apply a LIMIT, there will be the same number of rows from each state (+-1) - (assuming you have enough phone numbers for each state).

with numbered as (
 select i.*
 , row_number() over (partition by state order by phone) pos
 from ironman i
 -- put your WHERE clause here
), limited as (
 select *
 from numbered
 order by pos asc, state asc
 limit 6 -- set your limit here
)
select *
from limited
order by state, phone

db-fiddle

If you want the phone numbers to be selected randomly - change the ORDER for position

row_number() over (partition by state order by rand()) pos

share|improve this answer

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

add a comment | 

Use ROW_NUMBER() to assign the rows a position within a state group. Then order the rows by that postion. When you then apply a LIMIT, there will be the same number of rows from each state (+-1) - (assuming you have enough phone numbers for each state).

with numbered as (
 select i.*
 , row_number() over (partition by state order by phone) pos
 from ironman i
 -- put your WHERE clause here
), limited as (
 select *
 from numbered
 order by pos asc, state asc
 limit 6 -- set your limit here
)
select *
from limited
order by state, phone

db-fiddle

If you want the phone numbers to be selected randomly - change the ORDER for position

row_number() over (partition by state order by rand()) pos

share|improve this answer

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

with numbered as (
 select i.*
 , row_number() over (partition by state order by phone) pos
 from ironman i
 -- put your WHERE clause here
), limited as (
 select *
 from numbered
 order by pos asc, state asc
 limit 6 -- set your limit here
)
select *
from limited
order by state, phone

row_number() over (partition by state order by rand()) pos

share|improve this answer

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435

answered Mar 7 at 22:49

Paul SpiegelPaul Spiegel

17.6k32435