CODEWITHSUNDEEP
mysql
Matanya
Matanya

Reputation: 6346

Setting a LIMIT by a parameter other than number of rows

I have an application that searches and shows user recommendations of different Institutions.

Each Institution can have any number of recommendations.

The results are presented per institute like so:

I want to draw only ten Institutions at a time.

The problem is that my query refers to recommendations, and not to institutions, and therefore I end up getting 10 recommendations instead of 10 institutions.

Is there a way to tell MySQL that the limit refers to DISTINCT institutions, while still pulling in effect DUPLICATE institutions (i.e a number of recommendations on the same institute)?

The simpified versions of the actual query:

SELECT * FROM institutions
LEFT JOIN recommendations
  ON institutions.InstitutionID = recommendations.InstitutionID
WHERE [SOMETHING]
LIMIT 10

a different way to present the question: Is it possible to make the LIMIT clause refer to the original SELECT without the JOIN?

Upvotes: 1

Views: 137

Answers (3)

Bohemian
Bohemian

Reputation: 425258

Put the limit on institutions, then join from that:

SELECT * FROM (
    select *
    from institutions
    WHERE [SOMETHING about institutions]
    LIMIT 10) x
LEFT JOIN recommendations ON x.InstitutionID =  recommendations.InstitutionID
    AND [SOMETHING about recommendations]


For pagination in mysql, use this:

...
LIMIT 50, 10

This example gets the 6th page - starting at row 51 and getting 10 rows.
Note: offset counts from 0 not 1.

The syntax for limit in mysql is LIMIT offset, row_count. Without offset, it's LIMIT row_count (I know... the row_count parameter moves!)

You can also use the alternate syntax:

...
LIMIT 10 OFFSET 50

I find this latter syntax far easier to read.

Upvotes: 3

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

When you are joining make a sub query with the institution and put the limit there

SELECT * 
  FROM nsrecommendation
  JOIN (select * from nsserviceplace limit 10) as ten_service_places
 WHERE ten_service_places.NSServicePlaceID = nsrecommendation.NSServicePlaceID 
   AND [something];

Upvotes: 1

Bala
Bala

Reputation: 390

You could try some nested query to get the data you need if you only want to use mysql query as the way of retrieving the data. Can't tell much without the table structure.

Upvotes: 0

Related Questions