SQL SELECT DISTINCT number of times

Databases
1

I googled this one but couldn’t find the answer. So I thought I’d ask you lot.

I need to create a query that selects 3 occurrences of each distinct ‘userref’ from a table.
I can’t use DISTINCT because that will only give me one occurrence of each ‘userref’. I just wondered if there is any way of doing that?

Something like this:

SELECT * FROM announcements (but only the three first three occurrences of every distinct userref)
ORDER BY datesubmitted ASC

Anyone got any suggestions?

2 
SELECT * 
  FROM announcements AS t 
 WHERE ( SELECT COUNT(*)
           FROM announcements
          WHERE userref = t.userref
            AND datesubmitted < t.datesubmitted ) < 3
ORDER 
    BY datesubmitted ASC
3

Brilliant. Thanks

4

Hi Rudy,

don’t know if you are still listening but I tried to follow your suggestion and it doesn’t work yet. Here’s what I have:

$query_rs_deals = “SELECT * FROM deals AS t WHERE(SELECT COUNT(*) FROM deals WHERE userref =t.userref AND headline LIKE ‘%$search%’ OR description LIKE ‘%$search%’”.$where_clauses.“)<3 ORDER BY datestart”;

It is supposed to limit the results to 3 per user but it doesn’t. The page works okay though so it isn’t a total mess.
Can you, or anyone else see where I went wrong?

Dave

5

you’ve got an AND/OR problem

ANDs take precedence over ORs, much like in arithmetic where multiplication takes precedence over addition

4 + 3 * 5 is 19, not 35

is that enough of a hint?

you need to use parentheses

6

Afraid not. I’ve thrown parentheses in where ever I can but I can’t even get it to run. I’m just flying blind because I don’t really know what I am trying to isolate or why.
What page of your book should I look for the answer on?

7

page 59, Combining AND and OR :slight_smile:

SELECT * 
  FROM deals AS t 
 WHERE ( SELECT COUNT(*) 
           FROM deals 
          WHERE userref = t.userref 
            AND [COLOR="#FF0000"](
                headline LIKE '%$search%' 
             OR description LIKE '%$search%'
                )[/COLOR]
       ) < 3

you’ve also got some other php variable involved, $where_clauses, and you need to be careful where and how you append that

8

hmmm.

Here’s waht I have now:

“SELECT *
FROM deals AS t
WHERE(SELECT COUNT(*)
FROM deals WHERE userref =t.userref AND
(headline LIKE ‘%$search%’ OR description LIKE ‘%$search%’)”
.$where_clauses.
“<2 ORDER BY datestart”;

But that gives me a error like this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 5

Out of my depth I am afraid. Any suggestions.

Dave

PS - Found it on p89 - 90 “Use parentheses when Mixing AND and OR” Maybe I have an old edition.

9

Okay - saw it was missing the closing bracket so added that and now the page loads. But still doesn’t limit the results.

SELECT *
FROM deals AS t
WHERE(SELECT COUNT(*)
FROM deals WHERE userref =t.userref AND
(headline LIKE ‘%$search%’ OR description LIKE ‘%$search%’))"
.$where_clauses.
“<2 ORDER BY datestart”;

10

whenever you want assistance for a query, please echo the actual sql ~after~ php variable substitution

as for your problem, perhaps we should step back and re-examine exactly what you’re trying to achieve

can you state the problem in words?

in the first example, it was to return 3 rows for each userref, but only the 3 earliest

here, you seem to want 3 rows per userref, but… ?

p.s. yeah, my eyes are shot, i saw 59 when of course it’s 89 :blush:

11

each user may have entered hundreds of rows that match the search criteria. I just want to limit the results to show only the latest 3 from each user.
something like

SELECT ONLY 3 rows for each USERREF WHERE search matches

“whenever you want assistance for a query, please echo the actual sql ~after~ php variable substitution”
(Not sure how to do that ?)

12 
echo ( $query_rs_deals )

caution: i don’t do php :smiley:

13

okay, try this –

SELECT * 
  FROM deals AS t 
 WHERE (
       headline LIKE '%$search%' 
    OR description LIKE '%$search%'
       )
   AND ( SELECT COUNT(*) 
           FROM deals 
          WHERE userref = t.userref 
            AND (
                headline LIKE '%$search%' 
             OR description LIKE '%$search%'
                )
            AND datesubmitted < t.datesubmitted 
       ) < 3
14

Still doesn’t limit the number of entires for some reason

the sql echoed out is:

SELECT * FROM deals AS t WHERE ( headline LIKE ‘%%’ OR description LIKE ‘%%’ ) AND ( SELECT COUNT(*) FROM deals WHERE userref = t.userref AND ( headline LIKE ‘%%’ OR description LIKE ‘%%’ ) AND dateadded < t.dateadded ) < 3

15

please dump your tables so that i have something to test on