mercredi 19 juillet 2017

random join in SQL Server with a varying number of random results

SQL Server's SQL has become so clever that what looks like it might require a procedural solution can often be done with pure SQL. I am wondering if this is one of those times.
Let's say we have a STATES table and a CITIES table.

STATES:
State:  NY

CITIES
State: NY
City:  Armonk

Now let's complicate things with a third table: INSTRUCTIONS

INSTRUCTIONS
State: NY
HowMany: 17

State: NJ
HowMany: 11

Is there any way in SQL Server SQL to select HowMany cities at random from the CITIES table when the three tables are joined on State?

We don't know the "top N" in advance. It changes by state.

Of course, the States table will have all the 50 states, the Cities table all the cities in each state, and Instructions will have one record per state, identifying how many cities from that state are needed (chosen randomly).




Aucun commentaire:

Enregistrer un commentaire