CODEWITHSUNDEEP
mysqlsql
Frank Bauer
Frank Bauer

Reputation: 13

How can I limit the number of results of a specific part of an SQL query?

Can you see a way in the following SQL call to limit the number of results of just this specific part:

OR (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0)))

I like to limit that number of matches to a exact number, e.g. 150

The full SQL is: 

SELECT DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
      AND m.suspended = 0 
      AND (u1 = '2' OR u2 = '2' OR u3 = '2' OR (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))) GROUP BY mid

So, the result should include all where u1 = '2', all where u2 = '2', all where u3 = '2', but just 150 where:

(u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))

Upvotes: 1

Views: 103

Answers (2)

AK47
AK47

Reputation: 3807

If SQL SERVER Then use TOP , If MY SQL Then use LIMIT.

SQL-Server 

SELECT TOP 10 * FROM MyTable;

My SQL

SELECT * FROM MyTable LIMIT 10;

Upvotes: 1

Alexander
Alexander

Reputation: 20244

You can always make a subquery from every single part of your query and have it LIMITed to the n TOPmost entries.

From what I understand, something like this should suffice:

SELECT mid, did FROM(
SELECT DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
      AND m.suspended = 0 
      AND (u1 = '2' OR u2 = '2' OR u3 = '2')

UNION SELECT TOP 150 DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
     AND m.suspended = 0 
     AND (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))

) GROUP BY mid

Upvotes: 2

Related Questions