sql server - Top 12 from union queries filtering max 12 rows Ms Sql -

i facing issue, need 12 max rows 3 tables union data. if of table not have fixed rows other table should considered remaining rows.

for example: have 3 tables product category manufacturer

case 1 : if have 10 rows each should select 4 rows each table. case 2: if product table have 3 rows , category manufacturer have 5 rows each, should select 3 product , 5 category or manufacturer each. case 3: have less 4 rows shows many rows available.

please me acheive this. posting stored procedure have created till now.

  create procedure [dbo].[getrowsfromtables]     @searchterms nvarchar(150) begin  create table #search (id int identity ,ids int, productname nvarchar(200),  categoryname nvarchar(200), tagname nvarchar(200), sename nvarchar(200), displayorder int) insert #search     select top 12 p.id ids, p.name productname,  '' categoryname, '' tagname, '' sename, 0 displayorder product p p.published = 1 , p.deleted = 0 , name '%' + @searchterms + '%'      union      select top 12 c.id ids, '' productname, c.name categoryname, '' tagname, '' sename , 1 displayorder category c c.published = 1 , c.deleted = 0 , name  '%' + @searchterms + '%'      union      select top 12 t.id ids, '' productname, '' categoryname, t.name tagname, '' sename, 2 displayorder               manufacturer          name   '%' + @searchterms + '%'        select  id, ids,productname,categoryname, tagname, sename, tagpcount,tagproductid,      '' thumbnailimage, displayorder, (select count(*) #search displayorder = 0) ptotal,       (select count(*) #search displayorder = 1) ctotal,      (select count(*) #search displayorder = 2) tagtotal,       row_number() over(partition displayorder order id ) rn       #search         order displayorder      drop table #search end 

you can use windowed function row_number sequence records within union. order result row number:

/* generate 13 sample records:  *      product         (4)  *      category        (6)  *      manufacturer    (3)  */ product     (         -- sample product records.         select             id                     (                 values                     (1),                     (2),                     (3),                     (4)             ) x(id)     ),     category      (         -- sample category records.         select             id                     (                 values                     (1),                     (2),                     (3),                     (4),                     (5),                     (6)             ) x(id)     ),     manufacturer     (         -- sample manufacturer records.         select             id                     (                 values                     (1),                     (2),                     (3)             ) x(id)     ) select top 12     *      (             select                  row_number() on (order id) rn,                 'p' tbl,                 id                             product          union              select                 row_number() on (order id) rn,                 'c' tbl,                 id                             category          union              select                  row_number() on (order id) rn,                 'm' tbl,                 id                             manufacturer     ) u order     rn ; 

the order return records numbered 1 first, 2 , on. provide mix source data allows.

edit removed top each of subqueries. trying sql out limiting number of records had apply row number to. row_number applied before top, making pointless exercise. subqueries lacked order clause. without there no guaranteed order, making "top" pointless statement.