Slava Rozhnev

select 1, NEWID()
union select 2, NEWID()
union select 3, NEWID()
union select 4, NEWID()
union select 4, NEWID()
union select 6, NEWID()
union select 7, NEWID()
union select 8, NEWID()
union select 9, NEWID()
union select 10, NEWID();

Ограничения
При использовании в виде производной таблицы ограничение на количество строк отсутствует.

При использовании в виде предложения VALUES инструкции INSERT... VALUES применяется ограничение в размере 1000 строк. Если число строк превышает 1000, возвращается ошибка 10738. Чтобы вставить более 1000 строк, используйте один из следующих методов:

Создайте несколько инструкций INSERT

Используйте производную таблицу

Выполните массовый импорт данных, используя служебную программу bcp, класс SqlBulkCopy .NET, OPENROWSET (BULK ...) или инструкцию BULK INSERT.

SELECT [Case].[ID] 
FROM [Case]
JOIN [Instance] ON [Instance].[CaseID] = [Case].[ID]
GROUP BY [Case].[ID]
HAVING MAX([Level]) = 1;

select FORMAT(dt, 'yyyy-MM-dd') as d, max(t) as t
from T
group by FORMAT(dt, 'yyyy-MM-dd');

select max_t.*, FORMAT(T.dt, 'HH:mm') hour
from ( 
  select FORMAT(dt, 'yyyy-MM-dd') as date, max(t) as max_t
  from T
  group by FORMAT(dt, 'yyyy-MM-dd')
) max_t
join T on FORMAT(T.dt, 'yyyy-MM-dd') = max_t.date and  T.t = max_t.max_t;

SELECT 
	COUNT(DISTINCT CASE WHEN people.gender = 'M' THEN list.id END) AS mens,
	COUNT(DISTINCT CASE WHEN people.gender = 'F' THEN list.id END) AS womens,
	COUNT(DISTINCT CASE WHEN people.gender IS NULL THEN list.id END) AS unknown
FROM ListTable AS list 
LEFT JOIN PeopleTable AS people ON (list.userID = people.id) 
;

select sum(budget) from (
	select distinct project, budget from projects
) p;

with cte_orders(row_id, parent_id, group_name) as (
	select row_id, parent_id, group_name from Orders where row_id = @f
  	union all
  	select O.row_id, O.parent_id, O.group_name 
  	from Orders O 
  	join cte_orders on cte_orders.row_id = O.parent_id
) 
select sum(price) total_pice from cte_orders 
left join OrderItems on cte_orders.row_id = OrderItems.order_id ;

select 
	customer_id, 
	Customers.name,
	count(distinct order_id) all_orders,
	count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end) cash_machine_orders 
from Orders
join OrderItems on Orders.row_id = OrderItems.order_id
join Customers on Customers.row_id = customer_id
where Orders.registered_at between '2020/01/01' and '2020/12/31'
group by Customers.name, customer_id
having count(distinct order_id) = count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end)
;

select STRING_AGG(Name, ',') as Names, Salary from Workers group by Salary;

SELECT * FROM ( 
  -- SELECT MOST EXPENSIVE PRODUCT
  SELECT TOP(1) ProdID, Name 
  FROM Product 
  ORDER BY Price DESC 
) ExpensiveProd
JOIN (
  -- CALCULATE SALES BY PRODUCT IN DATE INTERVAL
  SELECT ProdID, SUM(Sum) ProdSales
  FROM Sales 
  WHERE Date BETWEEN '2019-07-01' AND '2019-07-31' GROUP BY ProdID
) ProdSales ON ProdSales.ProdID = ExpensiveProd.ProdID;

SELECT 
	orugie, sum_cena_orugie, pos, sum_cena_pos
FROM (
  	SELECT row_number() over (order by id_orugie_p) orugie_rn,  id_orugie_p orugie, SUM(cena) sum_cena_orugie
	FROM test
	GROUP BY id_orugie_p
) t_orugie
FULL JOIN (
	SELECT row_number() over (order by id_pos_p) pos_rn,  id_pos_p pos, SUM(cena) sum_cena_pos
	FROM test
	GROUP BY id_pos_p
) t_pos on orugie_rn = pos_rn;

select book_code, count(distinct bilet_number) lends
from lends
group by book_code
having count(distinct bilet_number) = (select count(distinct bilet_number) from abonents)