CODEWITHSUNDEEP
sqlrandomrows
user7948585
user7948585

Reputation:

selecting random rows from table

I have the following query, returning over 100 000 rows, How do I select random 8000 rows from(for example) vw_client_uli_member_type? Can someone provide an example on my query? 

    SELECT ind_cst_key,
ind_int_code as 'Individual Type',
ind_first_name as 'First Name',
ind_last_name as 'Last Name',
cst_recno as 'Member ID',
cst_eml_address_dn as 'Email Address',
adr_city as 'City',
adr_state as 'State' ,
adr_country as 'Country',
cst_org_name_dn as 'Company',
cst_ixo_title_dn as 'Job Title',
mem_member_type as 'Member Type'
FROM 
co_individual  WITH (NOLOCK) 
JOIN co_individual_ext  WITH (NOLOCK)  ON ind_cst_key_ext=ind_cst_key 
JOIN co_customer  WITH (NOLOCK)  ON cst_key=ind_cst_key and ind_delete_flag=0
and ind_deceased_flag=0 
LEFT JOIN co_customer_x_address  WITH (NOLOCK)  ON cst_cxa_key=cxa_key 
LEFT JOIN co_address  WITH (NOLOCK)  ON adr_key=cxa_adr_key 
LEFT JOIN vw_client_uli_member_type  WITH (NOLOCK)  ON cst_key=mem_cst_key 
WHERE mem_member_type Is Null AND adr_country = N'UNITED STATES' AND ind_deceased_flag != 1 AND ind_key_leader_flag_ext != 1 AND ind_int_code != N'Staff' AND ind_int_code != N'Spouse' AND ind_int_code != N'Press'
ORDER BY adr_country DESC

Upvotes: 0

Views: 47

Answers (2)

Bruno Fernandes
Bruno Fernandes

Reputation: 23

You can just add to yours query:

SELECT TOP 8000 (columns) FROM table
ORDER BY NEWID()

That should work.

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

This is what your code looks like after @brunofernandes suggested

    SELECT top 8000 ind_cst_key,
ind_int_code as 'Individual Type',
ind_first_name as 'First Name',
ind_last_name as 'Last Name',
cst_recno as 'Member ID',
cst_eml_address_dn as 'Email Address',
adr_city as 'City',
adr_state as 'State' ,
adr_country as 'Country',
cst_org_name_dn as 'Company',
cst_ixo_title_dn as 'Job Title',
mem_member_type as 'Member Type'
FROM 
co_individual  WITH (NOLOCK) 
JOIN co_individual_ext  WITH (NOLOCK)  ON ind_cst_key_ext=ind_cst_key 
JOIN co_customer  WITH (NOLOCK)  ON cst_key=ind_cst_key and ind_delete_flag=0
and ind_deceased_flag=0 
LEFT JOIN co_customer_x_address  WITH (NOLOCK)  ON cst_cxa_key=cxa_key 
LEFT JOIN co_address  WITH (NOLOCK)  ON adr_key=cxa_adr_key 
LEFT JOIN vw_client_uli_member_type  WITH (NOLOCK)  ON cst_key=mem_cst_key 
WHERE mem_member_type Is Null AND adr_country = N'UNITED STATES' AND ind_deceased_flag != 1 AND ind_key_leader_flag_ext != 1 AND ind_int_code != N'Staff' AND ind_int_code != N'Spouse' AND ind_int_code != N'Press'
ORDER BY NEWID(), adr_country DESC

Upvotes: 0

Related Questions