Geek asks and answers

Select the first x entries in the group

Use the query below to search for products that contain a term in the name. The query returns the desired results (often too many results).

Now I want him to be able to select a maximum of 3 (random) products for each company. Some companies return a lot of records / products, but I only need to take 3 and go to the next company.

SELECT p.title As entryname, cname 
FROM company c, product p
WHERE p.title LIKE '%steel%' AND p.cid = c.cid 
GROUP By cname, ca.title

I tried to figure out the section and ranking, but I did not go too far. I am using ms sql

+3
source share
3 answers

You can use ROW_NUMBER () for this

with cte as (
SELECT 
    p.title as entryname, cname,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id) rn
FROM company c
    INNER JOIN  product p 
    ON  p.cid = c.cid 
WHERE p.title LIKE '%steel%'
GROUP By cname, ca.title    
)
SELECT 
    p.title as entryname, cname,
FROM CTE where rn <= 3

If you really need a random one (instead of the 3 with the lowest ID), you can change the row_number string to

ROW_NUMBER() OVER (PARTITION BY c.id order by newid()) rn

+7
source

- SQL Server ( ms sql ), TOP MSDN:

SELECT TOP(3) p.title As entryname, cname 
FROM company c, product p
WHERE p.title LIKE '%steel%' AND p.cid = c.cid 
GROUP By cname, ca.title

Oracle rownum WHERE:

SELECT p.title As entryname, cname 
FROM company c, product p
WHERE p.title LIKE '%steel%' AND p.cid = c.cid AND rownum < 4
GROUP By cname, ca.title
0

You can just add

limit 3,5

until the end of your request. This will give you sequentially 5 lines, starting from line 3, as an example.

Now, to select these 5 rows randonly , it's a bit of a complicated mode, I think.

0
source

More articles:


All Articles