SQL, set limit for a column?

Question

I have a problem with this SQL-Query

SELECT * 
FROM page p 
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to IN
(
  'Art-Rock-Band',
  'Echo-Pop-Preisträger',
  'Englische_Band',
  'Genesis_(Band)',
  'Grammy-Preisträger',
  'Peter_Gabriel',
  'Phil_Collins',
  'Popband',
  'Progressive-Rock-Band',
  'Rock_and_Roll_Hall_of_Fame'
)

It works and I get a very big result of every page where p.page_id = c.cl_from

Now I want to set a limit for every single category because the query takes too long.

I want just 5 results for 'Art-Rock-Band', just 5 results for 'Echo-Pop-Preisträger' etc...

Answer 1

The solution to this is somewhat lengthy (unless someone else has a better idea) but you can use UNION ALL to display the top 5 results from a series of smaller queries following this pattern:

SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Art-Rock-Band'
LIMIT 5
UNION ALL
SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Echo-Pop-Preisträger'
LIMIT 5
...

You could also get fancy and replace the 5 with a variable so that you can control how many results you get from everything with a simple change:

DECLARE @num INT DEFAULT 5;

SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Art-Rock-Band'
LIMIT @num
UNION ALL
SELECT * FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Echo-Pop-Preisträger'
LIMIT @num
...

As an added value, I put in a second declaration (commented out, of course) of the same variable in the instance you might want to recall by percent rather than a set number.

I hope this helps point you in the right direction at least.

-C§

EDIT: For SQL Server, replace LIMIT @num with TOP @num before the UNION ALL in each query and replace the DEFAULT with =. You can also have a second line to declare the @num as a string and use the PERCENT keyword, but only in SQL Server as neither MySQL nor Oracle supports it.

For Oracle, you can replace it similarly with an addition to the WHERE clause: AND ROWNUM <= @num. You also want to update the DECLARE statement to prepend a colon to the equals so = becomes :=.

This should account for the primary differences from the above MySQL examples for any coming behind that have a similar question in the other two formats. More explanation can be found here: http://www.w3schools.com/sql/sql_top.asp.

Answer 2

SELECT  * 
FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to IN
(
  'Art-Rock-Band'
)
LIMIT 5
UNION ALL
SELECT  * 
FROM page p
LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to IN
(

  'Echo-Pop-Preisträger'
)
LIMIT 5

Edit: I found this link Get top n records for each group of grouped results and the solution is similiar to @CSS

Answer 3

This is the fastest way, do a top 5 on each category with a union and use that to select your data. This method will work faster if you have good indexes. You want them on page_id, cl_from, and cl_to.

Also note, I select only the page_id first before doing the union -- this allows a sql server to optimize performance.

SELECT * 
FROM page p 
join 
(
   select p.page_id 
   from page p 
   left join categorylinks c ON p.page_id = c.cl_from 
   where c.cl_to = 'Art-Rock-Band'
   limit 5

   union

   select p.page_id 
   from page p 
   left join categorylinks c ON p.page_id = c.cl_from 
   where c.cl_to = 'Echo-Pop-Preisträger'
   limit 5

   union 

   select p.page_id 
   from page p 
   left join categorylinks c ON p.page_id = c.cl_from 
   where c.cl_to = 'Englische_Band'
   limit 5

   union 

   ...

   select p.page_id 
   from page p 
   left join categorylinks c ON p.page_id = c.cl_from 
   where c.cl_to = 'Rock_and_Roll_Hall_of_Fame'
   limit 5
) sub on sub.page_id = p.page_id

Answer 4

Does this work for you:

SELECT *
FROM page p
WHERE p.page_id IN (
    SELECT c.cl_from
    FROM categorylinks
    WHERE c.cl_from = p.page_id
    AND c.cl_to IN (
        'Art-Rock-Band',
        'Echo-Pop-Preisträger',
        'Englische_Band',
        'Genesis_(Band)',
        'Grammy-Preisträger',
        'Peter_Gabriel',
        'Phil_Collins',
        'Popband',
        'Progressive-Rock-Band',
        'Rock_and_Roll_Hall_of_Fame'
    )
    LIMIT 5
);