CODEWITHSUNDEEP
mysqlselectsql-order-bydistinct
Niantic
Niantic

Reputation: 43

select distinct values and order by the same column(value)

I have tried to find something to help me with this, but no luck.

I have a column "slide_no" with values 1-4. I want to select everything from the database with distinct slide_no order by slide_no but show random selected images.

Database table

slide_id | slide_name  | slide_no | fk_project_id
  1      | pic1.jpg    |   1      |  2
  2      | pic2.jpg    |   3      |  4
  3      | pic3.jpg    |   2      |  3
  4      | pic4.jpg    |   4      |  1
  5      | pic5.jpg    |   2      |  6
  6      | pic6.jpg    |   3      |  5

As you can see, the slide_no don't have to be unique, and I want it to be able to show random images on each page load. So far, I have only been able to produce a query that get the first 4 images out.

Is it possible to order by slide_no and get random images, with different (still ordered by slide_no) images?

Thanks in advance.

Upvotes: 3

Views: 1088

Answers (4)

What have you tried
What have you tried

Reputation: 11148

Working example:

http://www.sqlfiddle.com/#!2/0664c/8

Select slide_name, slide_no From
(SELECT *
FROM ( SELECT *
    FROM tableName
    GROUP BY slide_no, slide_name 
    ORDER BY RAND()
) `tempTable`
 ) x
Group by slide_no
ORDER BY slide_no ASC
limit 4

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80653

MySQL provides ORDER BY RAND() for this.

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows.

The query will be:

SELECT *
FROM ( SELECT *
    FROM `tblName`
    GROUP BY slide_no 
    ORDER BY RAND()
) `tempTable`
ORDER BY slide_no ASC

Upvotes: 4

Josh
Josh

Reputation: 1601

SELECT slide_no
, ( SELECT TOP 1 slide_name 
    FROM #tmp T2 
    WHERE T2.slide_no = T1.slide_no     
    ORDER BY RAND() --You can place other logic to "randomize" here in the future
   )
FROM #tmp T1
GROUP BY slide_no;

I'm not sure if this gives you what you're looking for. The problem is that the repetitive calls of RAND() with the same seed value return the same results. If you want it to be truly random, that's another question in itself. See here for generating a random number in SQL.

Upvotes: 0

llanato
llanato

Reputation: 2503

You could try something like this if your just looking for a single random ID each time.

SELECT slide_id
FROM {table_name} 
ORDER BY RAND()
LIMIT 1;

Upvotes: 2

Related Questions