Geek asks and answers

How to get 10 best UNION results in sql?

I currently have the following code in a stored procedure (see below). In an attempt to return only 10 results, I accept the TOP 5 of each union. However, I would like to accept the TOP 10 of the UNION, and not necessarily 5 of them. Any ideas? Is it possible?

BEGIN
SELECT TOP 5
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'

UNION ALL

SELECT TOP 5 
    b_object_ID as [id], 
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'

ORDER BY [name]
END
+3
source share
5 answers

How about this?

SELECT TOP 10 *
FROM
(
SELECT a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
UNION ALL
SELECT b_object_ID as [id], 
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'
) x
ORDER BY [name]
+1
source

Yes, it is very possible. You just need to select the top 10 from the pooling results.

SELECT TOP 10 * FROM (
    SELECT
        a_object_ID as [id], 
        a_object_name as [name],
        'A_object' as [Type]
    FROM [database].[dbo].[table_a]
    WHERE a_object_name LIKE @Search + '%'

    UNION ALL

    SELECT
        b_object_ID as [id], 
        b_object_name as [name],
        'B_object' as [Type]
    FROM [database].[dbo].[table_b]
    WHERE b_object_name LIKE @Search + '%'
) u
ORDER BY u.[name]
+2
source

UNION

SELECT TOP 10 * 
FROM (
  SELECT
      a_object_ID as [id], 
      a_object_name as [name],
      'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'

  UNION ALL

  SELECT
      b_object_ID as [id], 
      b_object_name as [name],
      'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
) AS subquery
ORDER BY subquery.[name]
+2

? :

set rowcount 10

  SELECT a_object_ID as [id], 
         a_object_name as [name],
         'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'
UNION ALL
  SELECT b_object_ID as [id], 
         b_object_name as [name],
         'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
ORDER BY [name]

set rowcount 0
+1

, , . , .

declare @Recs int = 10;

with cte1 as (
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
, cte2 as
(
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
SELECT TOP (@Recs) [id], [name], [Type]
(
select [id], [name], [Type] from cte1
union all
select [id], [name], [Type] from cte2
) SQ
ORDER BY [Name]
0

More articles:


All Articles