1

Why isn't this query stopping at 15? This is my query.

SELECT title, type, profile
FROM playlists
WHERE active='1' AND feature='1' AND type='3' AND user='1'
ORDER BY RAND()
AND LIMIT 15

This results in dozens of records, I only want 15 random results to show each time. What am I overlooking?

david
  • 3,225
  • 9
  • 30
  • 43
Davo
  • 181
  • 1
  • 11
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... –  Dec 05 '18 at 04:28
  • $sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY RAND() AND LIMIT 15"; ------- YES - mysql..... – Davo Dec 05 '18 at 04:30
  • do you use mysql? – david Dec 05 '18 at 04:31
  • 1
    this is not standard SQL – Jodrell Dec 05 '18 at 04:45

3 Answers3

2

You need to remove the AND before the keyword LIMIT.

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY RAND()  LIMIT 15";
Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
  • Sorry, I thought I edited that out when I posted, I was trying several things, but officially the "AND" is not in the query... – Davo Dec 05 '18 at 04:36
2

You can try like below - you need to remove AND before LIMIT

SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' 
AND type='3' AND user='1' 
ORDER BY RAND() 
LIMIT 15
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • Tried that with no luck... Sorry, I thought I edited that out when I posted, I was trying several things, but officially the "AND" is not in the query... – Davo Dec 05 '18 at 04:39
  • how want you to see your order by by- expected output is much more appreciated @Davo – Fahmi Dec 05 '18 at 04:41
2

Here is an useful link for randomize the result. Check it here.

Your code only works on MySQL. For PostgreSQL and sqlite, use RANDOM(). For SQL Server, use NEWID(). For PostgreSQL and sqlite

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY RANDOM() LIMIT 15";

For SQl Server

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY NEWID() FETCH NEXT 15 ROWS ONLY";

Notice the difference usage of limit in SQL Server. There is a question for it. Link.

david
  • 3,225
  • 9
  • 30
  • 43
  • Tried that with no luck... Sorry, I thought I edited that out when I posted, I was trying several things, but officially the "AND" is not in the query... – Davo Dec 05 '18 at 04:41