All geek questions in one place

Join one table to get random rows from the second table

I have two tables: #tmpParams, which lists 29 combinations of Disability and Grade. The second table, #tmpSource contains many students with their StudentID, disability and class. I need to select 29 random strings from #tmpSource, joining #tmpParams by failure and class.

In other words, someone passed me a list of parameters for 29 students (disability and class), and they want 29 random students from #tmpSource, including StudentID, to match two fields.

This is DDL:

  IF OBJECT_ID('tempdb..#tmpParams') IS NOT NULL 
        BEGIN
              DROP TABLE #tmpParams
        END
  GO

  IF OBJECT_ID('tempdb..#tmpSource') IS NOT NULL 
        BEGIN
              DROP TABLE #tmpSource
        END
  GO

    /* Create parameter table */
    CREATE TABLE #tmpParams
         ( 
            Disability        varchar(10)
            ,Grade      varchar(10)
         )
    GO        

    /* populate it */
    INSERT INTO #tmpParams
        (Disability, Grade)
    SELECT '13 - AUT' ,'Grade 1'  UNION ALL
    SELECT '08 - SL' ,'Grade 1'  UNION ALL
    SELECT '11 - PSD' ,'Grade 1'  UNION ALL
    SELECT '04 - SLD' ,'Grade 2'  UNION ALL
    SELECT '04 - SLD' ,'Grade 2'  UNION ALL
    SELECT '08 - SL' ,'Grade 2'  UNION ALL
    SELECT '08 - SL' ,'Grade 2'  UNION ALL
    SELECT '08 - SL' ,'Grade 2'  UNION ALL
    SELECT '14 - TBI' ,'Grade 2'  UNION ALL
    SELECT '07 - PD' ,'Grade 2'  UNION ALL
    SELECT '08 - SL' ,'Grade 3'  UNION ALL
    SELECT '08 - SL' ,'Grade 3'  UNION ALL
    SELECT '08 - SL' ,'Grade 3'  UNION ALL
    SELECT '08 - SL' ,'Grade 3'  UNION ALL
    SELECT '08 - SL' ,'Grade 3'  UNION ALL
    SELECT '07 - PD' ,'Grade 3'  UNION ALL
    SELECT '04 - SLD' ,'Grade 4'  UNION ALL
    SELECT '04 - SLD' ,'Grade 4'  UNION ALL
    SELECT '08 - SL' ,'Grade 4'  UNION ALL
    SELECT '08 - SL' ,'Grade 4'  UNION ALL
    SELECT '08 - SL' ,'Grade 4'  UNION ALL
    SELECT '08 - SL' ,'Grade 4'  UNION ALL
    SELECT '08 - SL' ,'Grade 4'  UNION ALL
    SELECT '04 - SLD' ,'Grade 5'  UNION ALL
    SELECT '04 - SLD' ,'Grade 5'  UNION ALL
    SELECT '04 - SLD' ,'Grade 5'  UNION ALL
    SELECT '04 - SLD' ,'Grade 5'  UNION ALL
    SELECT '08 - SL' ,'Grade 5'  UNION ALL
    SELECT '08 - SL' ,'Grade 5';
    GO


    /* Create table to hold source */
    CREATE TABLE #tmpSource
         ( 
            StudentID          int
            ,Disability     varchar(10)
            ,Grade          varchar(10)
         )
    GO 

    INSERT INTO #tmpSource
        (StudentID, Disability, Grade)
    SELECT '3', '04 - SLD', 'Grade 2'  UNION ALL
    SELECT '31', '04 - SLD', 'Grade 2'  UNION ALL
    SELECT '39', '04 - SLD', 'Grade 2'  UNION ALL
    SELECT '46', '04 - SLD', 'Grade 2'  UNION ALL
    SELECT '10', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '13', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '25', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '33', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '57', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '60', '04 - SLD', 'Grade 4'  UNION ALL
    SELECT '8', '04 - SLD', 'Grade 5'  UNION ALL
    SELECT '19', '04 - SLD', 'Grade 5'  UNION ALL
    SELECT '23', '04 - SLD', 'Grade 5'  UNION ALL
    SELECT '51', '04 - SLD', 'Grade 5'  UNION ALL
    SELECT '55', '04 - SLD', 'Grade 5'  UNION ALL
    SELECT '16', '07 - PD', 'Grade 2'  UNION ALL
    SELECT '28', '07 - PD', 'Grade 2'  UNION ALL
    SELECT '36', '07 - PD', 'Grade 2'  UNION ALL
    SELECT '43', '07 - PD', 'Grade 2'  UNION ALL
    SELECT '11', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '14', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '26', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '34', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '58', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '61', '07 - PD', 'Grade 3'  UNION ALL
    SELECT '5', '08 - SL', 'Grade 1'  UNION ALL
    SELECT '21', '08 - SL', 'Grade 1'  UNION ALL
    SELECT '41', '08 - SL', 'Grade 1'  UNION ALL
    SELECT '48', '08 - SL', 'Grade 1'  UNION ALL
    SELECT '2', '08 - SL', 'Grade 2'  UNION ALL
    SELECT '30', '08 - SL', 'Grade 2'  UNION ALL
    SELECT '38', '08 - SL', 'Grade 2'  UNION ALL
    SELECT '45', '08 - SL', 'Grade 2'  UNION ALL
    SELECT '12', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '15', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '27', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '35', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '59', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '62', '08 - SL', 'Grade 3'  UNION ALL
    SELECT '9', '08 - SL', 'Grade 4'  UNION ALL
    SELECT '20', '08 - SL', 'Grade 4'  UNION ALL
    SELECT '24', '08 - SL', 'Grade 4'  UNION ALL
    SELECT '52', '08 - SL', 'Grade 4'  UNION ALL
    SELECT '56', '08 - SL', 'Grade 4'  UNION ALL
    SELECT '7', '08 - SL', 'Grade 5'  UNION ALL
    SELECT '18', '08 - SL', 'Grade 5'  UNION ALL
    SELECT '22', '08 - SL', 'Grade 5'  UNION ALL
    SELECT '50', '08 - SL', 'Grade 5'  UNION ALL
    SELECT '54', '08 - SL', 'Grade 5'  UNION ALL
    SELECT '4', '11 - PSD', 'Grade 1'  UNION ALL
    SELECT '32', '11 - PSD', 'Grade 1'  UNION ALL
    SELECT '40', '11 - PSD', 'Grade 1'  UNION ALL
    SELECT '47', '11 - PSD', 'Grade 1'  UNION ALL
    SELECT '6', '13 - AUT', 'Grade 1'  UNION ALL
    SELECT '17', '13 - AUT', 'Grade 1'  UNION ALL
    SELECT '42', '13 - AUT', 'Grade 1'  UNION ALL
    SELECT '49', '13 - AUT', 'Grade 1'  UNION ALL
    SELECT '53', '13 - AUT', 'Grade 1'  UNION ALL
    SELECT '1', '14 - TBI', 'Grade 2'  UNION ALL
    SELECT '29', '14 - TBI', 'Grade 2'  UNION ALL
    SELECT '37', '14 - TBI', 'Grade 2'  UNION ALL
    SELECT '44', '14 - TBI', 'Grade 2';
    GO

This fills 29 students in #tmpParams and 62 in #tmpSource. My real scenario is, of course, much more complicated.

29 #tmpSource ... #tmpParams, 29 , ?

   SELECT TOP 29
       TS.StudentID
       ,TS.Disability
       ,TS.Grade
   FROM #tmpSource AS TS
   ORDER BY NEWID();
   GO

, , , , , - .

+3
3

, - ,

SELECT TOP 29 TS.StudentID,
              TS.Disability,
              TS.Grade
FROM   #tmpParams P
       JOIN #tmpSource TS
         ON TS.Disability = P.Disability
            AND TS.Grade = P.Grade
ORDER  BY NEWID()

?

SELECT TS.StudentID,
       TS.Disability,
       TS.Grade
FROM   #tmpParams P
       CROSS APPLY (SELECT TOP 1 *
                    FROM   #tmpSource TS
                    WHERE  TS.Disability = P.Disability
                           AND TS.Grade = P.Grade
                    ORDER  BY NEWID()) TS

2

?

 ;WITH T1
     AS (SELECT *,
                Row_number() OVER (PARTITION BY Disability, Grade ORDER BY Newid()) RN
         FROM   #tmpSource),
     T2
     AS (SELECT Disability,
                Grade,
                Row_number() OVER (PARTITION BY Disability, Grade 
                                       ORDER BY (SELECT 0)) AS RN
         FROM   #tmpParams)
SELECT T1.Disability,
       T1.Grade,
       T1.StudentID
FROM   T1
       JOIN T2
         ON T1.Disability = T2.Disability
            AND T2.Grade = T1.Grade
            AND T1.RN = T2.RN
+2
with sample as
(
   select p.disability, p.grade, count(*) as numOfSamples from #tmpParams P
   group by p.disability, p.grade
),
rownumedStuds as
(
   select std.id, std.disability, std.grade, row_number() over (partition by std.disability, std.grade order by newID()) num
)
select a.id,a.disability, a.grade 
from sample b
inner join rownumedStuds a on a.grade = b.grade and a.disability = b.disability
where num <= numOfSamples

, , ( , , rownumedStuds, , .

,

+2

, , :

 /* Add identity column to #tmpParams */
 ALTER TABLE #tmpParams ADD ID INT IDENTITY (1,1)

 /* drop #tmpResults if exists */
       IF OBJECT_ID('tempdb..#tmpResults') IS NOT NULL 
             BEGIN
                   DROP TABLE #tmpResults
             END
       GO

 /* Create table to hold results */
 CREATE TABLE #tmpResults
  ( 
     StudentID          int
     ,Disability     varchar(10)
     ,Grade          varchar(10)
  )
 GO 

 DECLARE @i AS INT; -- set variable to parse through 
 SET @i = 1
 WHILE @i <= (SELECT MAX(ID) FROM #tmpParams) -- Max number in table
 BEGIN
     INSERT INTO #tmpResults
         SELECT TOP 1
             TS.StudentID
             ,TS.Disability
             ,TS.Grade
         FROM #tmpSource AS TS
         JOIN #tmpParams AS TP
             ON TS.Disability = TP.Disability
             AND TS.Grade = TP.Grade
         WHERE TP.ID = @i 
             AND TS.StudentID NOT IN (SELECT StudentID FROM #tmpResults)  -- so no duplicates
         ORDER BY NEWID()
     SET @i = @i + 1
 END;
 GO

, , .

0
source

Source: https://habr.com/ru/post/1792833/

More articles:


All Articles