Having Joins query with limit 10 on second table

I have designer and design table

designer has many designs

I want to get all designers with 10 designs for each designer

Is there way I can do it in a single query in either postgresql, mysql

-------------Problems Reply------------

For best performance in Postgres (not possible in MySQL) use a LATERAL join:

SELECT d.designer, d1.design -- whatever columns you need
FROM designer d
LEFT JOIN LATERAL (
SELECT * -- or just needed columns
FROM design
WHERE designer_id = d.designer_id
-- ORDER BY ??? -- you may want to pick certain designs
LIMIT 10
) d1 ON true
ORDER BY d.designer_id, d.design_id; -- or whatever

This gives you 10 designs per designer - or as many as he/she has if there are fewer.

  • LEFT JOIN LATERAL ... ON true keeps designers in the result that don't have a single design (yet).

    • What is the difference between LATERAL and a subquery in PostgreSQL?
    • Call a set-returning function with an array argument multiple times
  • You get best performance if you add an ORDER BY clause that matches an existing index on design like:

    CREATE INDEX foo ON design (designer_id, design_id)

    Then, in the subquery d1 in above query:

    ...
    ORDER BY design_id
    ...

    Now Postgres can pick the top items from the index directly.

Related answer with more details:

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

select * from (
Select row_number() OVER (PARTITION BY a.designer ORDER BY b.design DESC) rn,
a.* ,b.*
from a
inner join b
on a.id = b.id
)
where rn <= 10

mysql doesn't have window functions, which you need, so this is postgresql.

In MySQL, you can make use of user-defined variables to emulate some of the analytic functions available in other databases...

SELECT v.designer_id
, v.design_id
FROM ( SELECT @rn := IF(r.id = @prev_r_id,@rn+1,1) AS rn
, @prev_r_id := r.id AS designer_id
, d.id AS design_id
FROM (SELECT @rn := 0, @prev_r_id := NULL) i
CROSS
JOIN designer r
LEFT
JOIN design d
ON d.designer_id = r.id
ORDER BY r.id, d.id
) v
WHERE v.rn <= 10
ORDER BY v.designer_id, v.design_id

You can run just the inline view query (v) to see what that returns. What it does is order the rows by designer_id, and compares the value from the current row to the value from the previous row... if they match, it increments @rn by 1, otherwise, it resets @rn to 1. Net effect is that we get an ascending integer sequence from rn for each designer_id... 1,2,3.

The outer query filters out the rows where rn is greater than 10.

If a given designer has fewer than ten designs, we get fewer than ten rows for that designer.

Category:mysql Views:1 Time:2019-01-09

Related post

  • I have two tables in Access, and I want to join the tables so that I can run a query with data from both tables. How do I join tables in Access? 2014-10-25

    I have two tables in Access, and I want to join the tables so that I can run a query with data from both tables. How do I join tables in Access? --------------Solutions------------- Hi Stephanie, Is the data in these two tables related? If so, how ar

  • What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause? 2009-09-17

    What is the difference between setting statement fetch size in JDBC or firing a SQL query with LIMIT clause? --------------Solutions------------- The SQL LIMIT will limit your SQL query results to those that fall within a specified range. You can use

  • query without limit works faster than query with limit 2014-12-29

    What is the explanation why the same query with limit 100 works slower than similar query without limit 100. The two queries run against the same database and and the result-set is less than 100 The original query was generated by hibernate and had s

  • How to write complex join query with JPA 2011-08-09

    How to write this kind of complex join query with JPA, Some of syntax I have denoted below not work with JPA. I have used them for demonstrate way that sql query should build, so sorry about that. SELECT Result1.name1, Result1.count1, Result2.name2,

  • Displaying one result on a query with LIMIT 10 2011-08-16

    Today i'm makeing this new question because this is not posible to display one result on a query with limit 10. Here is the query: $query = "SELECT * FROM articol WHERE status = 1 ORDER BY data DESC LIMIT 10"; $result = mysql_query($query) or die ("C

  • echo full joomla query (with limit etc)? 2012-03-07

    I was wondering if there was a way to echo out the full query with limit and limitstart etc. I can echo out the line $query, but i want to see why the limit isn't working and I can't seem to get it to display the actual query that it's sending to the

  • SQL query with limit on rows from one table, not the result set 2010-01-18

    I'm running a simple query with a join, similar to SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON ... LIMIT 5 As t1 has-many rows in t2 ( any number above 2 ) the LIMIT statement does not return the first 5 rows from t1 and corresponding entries from t2,

  • Join query with multiple tables invovled 2010-07-26

    I am using Join in sql for first time with respect to many tables, I have got error with this : I have three tables, Semester table StudentID Department Semester 1 1 1 Course Table CourseID CourseName Semester 1 S.E 1 2 D.B 1 ExamAttend Table(foreign

  • How to join results from Facebook api query with my own MySQL table 2013-06-27

    What is the best way to join the results of a Facebook api query with my own MySQL database? I am trying to display a list of blogs that my friends are following. I am using the android Facebook sdk 3.0. Here is how I am getting a list of my friends,

  • How to create a mysql join query with hierarchical data 2011-03-10

    I need to create a join query for hierarchical data across 2 tables. These tables can have unlimited amounts of data and their structures are as follows: group_id group_name group_order 1 group 1 2 2 group 2 1 field_id field_name parent_group field_o

  • Update query with fields from 1 table to another 2012-03-15

    I have a process that pulls data from other databases to use for mailings. I have matched the records to see if address changes have occured. I need to update my table from the new data if it needs updates. I have tried update queries but it doesn't

  • Optimizing select query with limit and order by 2011-04-20

    Following is my query: select * from table order by timestamp desc limit 10 this takes too much time compared to select * from table limit 10 How can I optimize the first query to get to near performance of second query. UPDATE: I don't have control

  • Crosstab query with category totals, tree table structure 2011-05-25

    How would I go about making a crosstab query like this? TopItem1 TopItem2 Category1 Ca1Item1 Ca1Item2 Category2 Ca2Sub1 Ca2Sub1It1 Ca2Sub1It2 Ca2Sub2 Ca2Sub2It1 Group1 1 3 6 2 4 4 3 1 2 1 1 Group2 3 1 0 0 0 5 4 2 2 1 1 i.e. a crosstab query with tota

  • Join query with JPA? 2011-07-29

    I'm having a hard time with this join query in my application. I have two classes, Encounter and CheckedEncounter. CheckedEncounter is a temporary model that I'm using to manage the Encounters that have already been processed. It looks like this: @En

  • select with limit and update table. Limit not working fine. sql 2012-01-16

    I am trying to update thable with select using limit It's update table fine when we enter "limit" only with one parameter in select query like (limit 50) But when select with "limit" like (limit $sqlFrom, $sqlTo) it updates the table but skip 2nd (51

  • Using a self join query with a lookup field 2013-10-31

    Access 2007: I have a self join query. When I try to use it as the record source in a lookup field, the Lookup Wizard tells me "You may have selected a query that uses the table you're adding the lookup column to. Please select a new source." Is ther

  • ACCESS 2003: "cannot delete from specified tables" error on delete query with a SINGLE/unrelated table 2013-01-24

    I've hunted around on the web and see various answers to what is stumping me - a fairly novice user. I have a 5-table DB that's related. I want to create a mailing list, but my DB has several members that have the same address (eg a husband and wife

  • how do I write that join query with ActiveRecord? 2009-09-07

    anyone know how I would write that query with AR? select *, (m.user_id=1) as member from band b join memberships m on m.band_id = g.id; Thanks in advance. --------------Solutions------------- The assumption here is that you have something that looks

  • LINQ join query with multiple fields using left outer join 2010-10-22

    I have Product table and Officer table as follow: Product ProductID | ProductName | Officer1ID | Officer2ID | Officer3ID --------- | ----------- | ---------- | ---------- | ---------- 12 | Mouse | 123 | 124 | 125 13 | Keyboard | 234 | 235 | 0 Officer

  • Doctrine update query with LIMIT 2012-01-17

    I would like to do an update-query with a LIMIT like that: UPDATE anytable SET anycolumn = 'anyvalue' WHERE anothercolumn='anothervalue' LIMIT 20 How is this possible with doctrine 2.1 ? --------------Solutions------------- Not doctrine specific, but

  • How to limit to 1 item on Left join query with JPA? 2012-01-24

    Here's my use case : public class User extends Model {} public class TableA extends Model { @OneToMany public List<TableB> tableBs; @ManyToOne public AnObject anObject; } public class TableB extends Model { @ManyToOne public TableA tableA; @Man

  • Codeginiter JOIN query with DATE between and LIMIT not working 2013-12-11

    I am trying to get the details of a persons based on the user input age (Integer). Now my query always returns null Array() whenever I execute the below code. I haven't specified the $postdata array. You can see I have used $postdata['ageto'] and $po

  • Joining Query with Multiple Tables Based on Values 2011-11-01

    Supposed I have a table posts like below: | id | type | ref_id | -----------+--------------+ --------------- | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 3 | 3 | | 4 | 1 | 4 | | 5 | 2 | 5 | | 6 | 3 | 6 | The type field is to join with other tables, so for examp

  • SQL: How to make a join query with only having the "largest" entry of any "type"? 2012-02-01

    I have two tables: Table eventtypes and Table events with one-to-many relation. Every event has a timestamp. Now I would like to have an SQL query that returns me every eventtype + its corresponding event with the latest timestamp (which I can guaran

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.091 (s). 11 q(s)