Limit Results from Joined Table to One Row

SELECT p.*,
       pp.*
  FROM PRODUCTS p
  JOIN PRODUCT_PHOTOS pp ON pp.product_id = p.product_id
  JOIN (SELECT x.product_id,
               MIN(x.photo_order) AS default_photo
          FROM PRODUCT_PHOTOS x
      GROUP BY x.product_id) y ON y.product_id = pp.product_id
                              AND y.default_photo  = pp.photo_order

post_id	comment_id	like_votes	votes_rank
0	1	5	1
0	2	7	2
1	4	3	1
1	3	10	2

Limit result of inner join to 1

Since no logic has been provided for determining which row is the 'first record' (see @HoneyBadger's comments), and you've commented that ordering is not important for your business case, we'll use max() to extract a single row per unique orderid:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op,

        (select op2.orderid,
                max(op2.descr) as descr
        from    orderpos op2
        group by op2.orderid
        ) dt

where   op.orderid = o.id
and     op.orderid = dt.orderid
and     op.descr   = dt.descr
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

Your join criteria between ord(o) and orderpos(op) remains the same; the addition of the derived table (dt) allows us to further limit the rows that are of interest from orderpos(op).

In this case it's just a coincidence that our use of max() generated the output you're looking for. [HINT: replace max() with min() to display '2/blub' instead of '1/huba'.]

---

Same idea but using a correlated sub-query instead of a derived table:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op

where   op.orderid = o.id
and     op.descr = (select      max(op2.descr)
                        from    orderpos op2
                        where   op2.orderid = op.orderid)
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

---

Alternatively we could have replaced max(op2.descr) with max(op2.id).

The key issue being to pick some method ... any method in this case ... that allows us to pick a single row from op2 for a given orderid (via the group by op2.orderid).

NOTE: The proposed solutions work as long a given descr value is unique for a given orderid (eg, you can't have 2x rows in orderpos with the same orderid and descr). If this is an invalid assumption then we'll need more sample data and/or a better description of the data in the orderpos table (eg, pk contraint, unique index, etc).

How to limit and search number of joined rows from table in multiple joins in mysql

In MySql >= 8.0 you can number the rows using some criteria (for each Form starting from one and order by u.role ASC and u.id ASC), then you can filter rows with number one:

WITH sq AS (SELECT u.id AS user_id, f.id AS form_id, u.name, f.type, uf.additional_comment,
                   ROW_NUMBER() OVER (PARTITION BY f.id ORDER BY u.role ASC, u.id ASC) AS num
       
            FROM Forms AS f
            LEFT JOIN UsersAssignToForms AS uf ON f.id = uf.form_id
            LEFT JOIN Users AS u ON u.id = uf.user_id)
            
SELECT *
FROM sq
WHERE num = 1;

Before MySql 8.0 you can try something like this (the idea is the same but with different implementation):

SELECT sq2.user_id, sq2.form_id, sq2.name, sq2.type, sq2.additional_comment
FROM (
    SELECT 
           sq1.*,
           
           @row_number:=CASE WHEN @form_id = sq1.form_id THEN @row_number + 1 ELSE 1 END AS num,
           @form_id:= sq1.form_id
        
    FROM (SELECT u.id AS user_id, f.id AS form_id, u.name, f.type, uf.additional_comment
          FROM Forms AS f
          LEFT JOIN UsersAssignToForms AS uf ON f.id = uf.form_id
          LEFT JOIN Users AS u ON u.id = uf.user_id
          ORDER BY f.id ASC, u.role ASC, u.id ASC) AS sq1
          
    ORDER BY sq1.form_id) AS sq2

WHERE sq2.num = 1;

Implementing LIMIT against rows of single table while using LEFT JOIN

A query like:

SELECT * FROM items ORDER BY ? LIMIT 10

will return 10 rows (at most) from items.

You need to provide the column(s) for the ORDER BY clause if you have some specific sorting condition in mind.

If not then remove the ORDER BY clause, but in this case nothing guarantees the resultset that you will get.

So all you have to do is LEFT join the above query to images:

SELECT it.*, im.*
FROM (SELECT * FROM items ORDER BY ? LIMIT 10) it
LEFT JOIN images im
ON im.item_id = it.id

Limit join to one row

SELECT (count(*) * sum(s."price")) AS amount
     , 'rma'       AS "creditType"
     , c."company" AS "client"
     , c.id        AS "ClientId"
     , r.* 
FROM   "Rmas"            r
JOIN   "EsnsRmas"        er ON er."RmaId" = r."id"
JOIN   "Esns"            e  ON e.id = er."EsnId"
JOIN  (
   SELECT DISTINCT ON ("EsnId") *
   FROM   "EsnsSalesOrderItems"
   ORDER  BY "EsnId", "createdAt" DESC
   )                     es ON es."EsnId" = e."id"
JOIN   "SalesOrderItems" s  ON s."id" = es."SalesOrderItemId"
JOIN   "Clients"         c  ON c."id" = r."ClientId"
WHERE  r."credited" = FALSE
AND    r."verifyStatus" IS NOT NULL 
GROUP  BY c.id, r.id;

Your query in the question has an illegal aggregate over another aggregate:

sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount

Simplified and converted to legal syntax:

(count(*) * sum(s."price")) AS amount

But do you really want to multiply with the count per group?

I retrieve the the single row per group in "EsnsSalesOrderItems" with DISTINCT ON. Detailed explanation:

• Select first row in each GROUP BY group?

I also added table aliases and formatting to make the query easier to parse for human eyes. If you could avoid camel case you could get rid of all the double quotes clouding the view.

Inner join limit the rows from second table

Select the ten transactions in a subquery:

select *
from
(
  select *
  from transactions
  order by time desc
  limit 10
) t
join queries q on q.id like concat(t.tid, '%')
order by t.time desc, t.tid, q.timestamp desc;

Limit number of rows per group from join (NOT to 1 row)

row_number()

The general solution to get the top n rows per group is with the window function row_number():

SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY store_id ORDER BY employee_id) AS rn
   FROM   employees
   WHERE  currently_employed
   ) e
JOIN   stores s USING (store_id)
WHERE  rn <= 15
ORDER  BY store_id, e.rn;

• PARTITION BY should use store_id, which is guaranteed to be unique (as opposed to store_name).

• First identify rows in employees, then join to stores, that's cheaper.

• To get 15 rows use row_number() not rank() (would be the wrong tool for the purpose). (While employee_id is unique, the difference doesn't show.)

LATERAL

An alternative since Postgres 9.3 that typically performs (much) better in combination with a matching index, especially when retrieving a small selection from a big table. See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

SELECT s.store_name, e.*
FROM   stores s
CROSS  JOIN LATERAL (
   SELECT *  -- better just the needed columns!
   FROM   employees e
   WHERE  e.store_id = s.store_id
   AND    e.currently_employed
   ORDER  BY e.employee_id
   LIMIT  15
   ) e
-- WHERE ... work with selected stores?
ORDER  BY s.store_name, e.store_id, e.employee_id;

The perfect index would be a partial multicolumn index like this:

CREATE INDEX ON employees (store_id, employee_id) WHERE  currently_employed;

Related example:

• Create unique constraint with null columns

Both versions exclude stores without current employees. There are ways around this if needed with LEFT JOIN LATERAL ...

MySQL Limit Results Based on Join Table

MySQL has no TOP keyword. It does however have a LIMIT keyword. Your query is invalid anyway.

There are a couple of options here. The following is an example of a correlated subquery: https://en.wikipedia.org/wiki/Correlated_subquery

SELECT 
    a.idAuthor, 
    a.Name , 
    (SELECT COUNT(*) from author_has_publication ahp WHERE   
        ahp.Author_idAuthor = a.idAuthor) AS publication_count
FROM 
    author a
ORDER BY 
    publication_count DESC
LIMIT 2

As the referenced article notes, the above is inefficient as the subquery needs to be re-executed for each row of the result. If you do not actually need the count in the resultset then the below would be more efficient as the subquery is non-correlated and executed only once.

SELECT 
    a.idAuthor, 
    a.Name 
FROM 
    author a
INNER JOIN 
    (select ahp.Author_idAuthor AS idAuthor, COUNT(*) as publication_count 
        FROM author_has_publication ahp GROUP BY ahp.Author_idAuthor LIMIT 2) 
        AS TEMP ON TEMP.idAuthor = a.idAuthor

---

Related Topics