Order by something THEN rand() ?

[TEENFRONT]

Hey

 

I have a custom made link script. And i wish to send a link out to the top 3 referrers.

 

my db is like this

 

id | siteurl | hits_in

1  | www. | 5

2  | www. | 2

13 | www. | 8

 

So i first want to ORDER BY hits_in DESC    so i get results ordered by highest hits in, then LIMIT 5

 

Soooo... i get the top 5 referrers from the list. But here i get stuck. I just want to choose 1 rand() result  out of the top 5 from the previous ORDER BY.

 

I need a query like

 

ORDER BY hits_in DESC LIMIT 5 rand() LIMIT 1

 

I know thats not logical so how do i tackle this?

 

Mny thanks!

[xtopolis]

SELECT id,siteurl,hits_in FROM `yourDB` WHERE hits_in IN (SELECT hits_in FROM `yourDB` ORDER BY hits_in DESC) ORDER BY RAND() LIMIT 5

 

5 minutes, google.  Wrong forum board.   You're welcome.

[bluejay002]

make use of derived table:

 

SELECT *

FROM (

  SELECT *

  FROM db

  ORDER BY hits_in DESC LIMIT 5

) dummy

ORDER BY RAND() LIMIT 1;

 

cheers,

 

Jay

[TEENFRONT]

make use of derived table:

 

SELECT *

FROM (

  SELECT *

  FROM db

  ORDER BY hits_in DESC LIMIT 5

) dummy

ORDER BY RAND() LIMIT 1;

 

cheers,

 

Jay

 

I think this is what i want . carnt test till tonight. many many thanks!

[bluejay002]

welcome. just drop by if there are still some problems with that.