I am randomly generating millions of rows in a table. When adding a row I want to randomly select one of the rows from the Country table which I show below and get the name to insert it into the table. I don't want the Id of the country I want the name, as this is helping me populate the other table with the name.
I have a Country table which has 249 rows, I want to get the country name randomly with this SQL:
SELECT TOP 1 [Name] FROM Country WHERE CountryId = ROUND(RAND(CHECKSUM(NEWID()))*(249-1) 1, 0)
But it doesn't always have a value and sometimes has more than 1 value! This is very odd.
I am running this SQL to test it now:
SELECT * FROM Country WHERE CountryId = ROUND(RAND(CHECKSUM(NEWID()))*(249-1) 1, 0)
I get results, multiple results and no results. I must be missing something as I just want the one number and always one number?
Below I show 3 results from running it:
The countries are as follows:
INSERT INTO dbo.[Country] ([Name])
VALUES ('Afghanistan'),
('Åland Islands'),
('Albania'),
('Algeria'),
('American Samoa'),
('Andorra'),
('Angola'),
('Anguilla'),
('Antarctica'),
('Antigua and Barbuda'),
('Argentina'),
('Armenia'),
('Aruba'),
('Australia'),
('Austria'),
('Azerbaijan'),
('Bahrain'),
('Bahamas'),
('Bangladesh'),
('Barbados'),
('Belarus'),
('Belgium'),
('Belize'),
('Benin'),
('Bermuda'),
('Bhutan'),
('Bolivia, Plurinational State of'),
('Bonaire, Sint Eustatius and Saba'),
('Bosnia and Herzegovina'),
('Botswana'),
('Bouvet Island'),
('Brazil'),
('British Indian Ocean Territory'),
('Brunei Darussalam'),
('Bulgaria'),
('Burkina Faso'),
('Burundi'),
('Cambodia'),
('Cameroon'),
('Canada'),
('Cape Verde'),
('Cayman Islands'),
('Central African Republic'),
('Chad'),
('Chile'),
('China'),
('Christmas Island'),
('Cocos (Keeling) Islands'),
('Colombia'),
('Comoros'),
('Congo'),
('Congo, the Democratic Republic of the'),
('Cook Islands'),
('Costa Rica'),
('Côte d''Ivoire'),
('Croatia'),
('Cuba'),
('Curaçao'),
('Cyprus'),
('Czech Republic'),
('Denmark'),
('Djibouti'),
('Dominica'),
('Dominican Republic'),
('Ecuador'),
('Egypt'),
('El Salvador'),
('Equatorial Guinea'),
('Eritrea'),
('Estonia'),
('Ethiopia'),
('Falkland Islands (Malvinas)'),
('Faroe Islands'),
('Fiji'),
('Finland'),
('France'),
('French Guiana'),
('French Polynesia'),
('French Southern Territories'),
('Gabon'),
('Gambia'),
('Georgia'),
('Germany'),
('Ghana'),
('Gibraltar'),
('Greece'),
('Greenland'),
('Grenada'),
('Guadeloupe'),
('Guam'),
('Guatemala'),
('Guernsey'),
('Guinea'),
('Guinea-Bissau'),
('Guyana'),
('Haiti'),
('Heard Island and McDonald Islands'),
('Holy See (Vatican City State)'),
('Honduras'),
('Hong Kong'),
('Hungary'),
('Iceland'),
('India'),
('Indonesia'),
('Iran, Islamic Republic of'),
('Iraq'),
('Ireland'),
('Isle of Man'),
('Israel'),
('Italy'),
('Jamaica'),
('Japan'),
('Jersey'),
('Jordan'),
('Kazakhstan'),
('Kenya'),
('Kiribati'),
('Korea, Democratic People''s Republic of'),
('Korea, Republic of'),
('Kuwait'),
('Kyrgyzstan'),
('Lao People''s Democratic Republic'),
('Latvia'),
('Lebanon'),
('Lesotho'),
('Liberia'),
('Libya'),
('Liechtenstein'),
('Lithuania'),
('Luxembourg'),
('Macao'),
('Macedonia, the Former Yugoslav Republic of'),
('Madagascar'),
('Malawi'),
('Malaysia'),
('Maldives'),
('Mali'),
('Malta'),
('Marshall Islands'),
('Martinique'),
('Mauritania'),
('Mauritius'),
('Mayotte'),
('Mexico'),
('Micronesia, Federated States of'),
('Moldova, Republic of'),
('Monaco'),
('Mongolia'),
('Montenegro'),
('Montserrat'),
('Morocco'),
('Mozambique'),
('Myanmar'),
('Namibia'),
('Nauru'),
('Nepal'),
('Netherlands'),
('New Caledonia'),
('New Zealand'),
('Nicaragua'),
('Niger'),
('Nigeria'),
('Niue'),
('Norfolk Island'),
('Northern Mariana Islands'),
('Norway'),
('Oman'),
('Pakistan'),
('Palau'),
('Palestine, State of'),
('Panama'),
('Papua New Guinea'),
('Paraguay'),
('Peru'),
('Philippines'),
('Pitcairn'),
('Poland'),
('Portugal'),
('Puerto Rico'),
('Qatar'),
('Réunion'),
('Romania'),
('Russian Federation'),
('Rwanda'),
('Saint Barthélemy'),
('Saint Helena, Ascension and Tristan da Cunha'),
('Saint Kitts and Nevis'),
('Saint Lucia'),
('Saint Martin (French part)'),
('Saint Pierre and Miquelon'),
('Saint Vincent and the Grenadines'),
('Samoa'),
('San Marino'),
('Sao Tome and Principe'),
('Saudi Arabia'),
('Senegal'),
('Serbia'),
('Seychelles'),
('Sierra Leone'),
('Singapore'),
('Sint Maarten (Dutch part)'),
('Slovakia'),
('Slovenia'),
('Solomon Islands'),
('Somalia'),
('South Africa'),
('South Georgia and the South Sandwich Islands'),
('South Sudan'),
('Spain'),
('Sri Lanka'),
('Sudan'),
('Suriname'),
('Svalbard and Jan Mayen'),
('Swaziland'),
('Sweden'),
('Switzerland'),
('Syrian Arab Republic'),
('Taiwan, Province of China'),
('Tajikistan'),
('Tanzania, United Republic of'),
('Thailand'),
('Timor-Leste'),
('Togo'),
('Tokelau'),
('Tonga'),
('Trinidad and Tobago'),
('Tunisia'),
('Turkey'),
('Turkmenistan'),
('Turks and Caicos Islands'),
('Tuvalu'),
('Uganda'),
('Ukraine'),
('United Arab Emirates'),
('United Kingdom'),
('United States'),
('United States Minor Outlying Islands'),
('Uruguay'),
('Uzbekistan'),
('Vanuatu'),
('Venezuela, Bolivarian Republic of'),
('Viet Nam'),
('Virgin Islands, British'),
('Virgin Islands, U.S.'),
('Wallis and Futuna'),
('Western Sahara'),
('Yemen'),
('Zambia'),
('Zimbabwe')
CodePudding user response：
The problem is that the calculation is being re-evaluated on every row, therefore it's possible for completely arbitrary results to appear.
Ideally, you should just choose the TOP 1 ORDER BY NEWID() solution, but if you're set on calculating an actual ID, you could store it in a variable first
DECLARE @id int = ROUND(RAND(CHECKSUM(NEWID()))*(249-1) 1, 0);
SELECT TOP 1 [Name]
FROM Country
WHERE CountryId = @id;
You may still get no result, because there may not actually be that ID in the table. But you won't get multiple results.
CodePudding user response：
It's not clear how you're generating your set of random rows, however the following is one way you can generate a random country name for each row of a table.
You should be able to apply this to your specific situation as part of an insert into.
You can use apply to generate the random number and a further apply to select the random value per row.
This example simply uses an existing table but generates a random country name for each row and assumes the id values are sequential 1 thru 249:
select v.number, c.name
from master.dbo.spt_values v
cross apply (values(Abs(Checksum(NewId())) % 249))x(r)
cross apply (
select top(1) [name]
from country c
where c.id=r
)c
order by v.number
CodePudding user response：
Try this
select top 1 * from [Table] order by newid()
select top 1 * from Country order by newid()
for better performance, you can use this approach
select TOP 1 * FROM Country where CountryId in
(select top 1 * CountryId from Country order by newid())
for limited rows in coutry id between 1 and 249, you can use this:
;with LimitedRows
AS
(
Select * From FROM Country
Where CountryId >= 1 and CountryId <=249)
)
select top 1 * from LimitedRows order by newid()
CodePudding user response：
Rather than trying to generate an ID at random, you can specify a random sort, and TOP 1.
SELECT TOP 1 *
FROM Country
ORDER BY NEW_ID();
NEW_ID() generates random uniqueidentifiers (one per row), and ordering by that function will induce a random order. Then selecting the first of the randomized rows gives you exactly one random row.