With SQL do order and randomize at the same time?

Question

With SQL do order and randomize at the same time?

Navigation

#1 by (-1 votes)
#2 by (-2 votes)

-2

I have the following code:

(SELECT id, slug 
 FROM anuncios 
 WHERE vum = '$idcategoria'
 ORDER BY RAND()) 
ORDER BY publicado DESC

I need SQL to sort and at the same time randomize, except that the above code is just doing the sorting, randomization does not work.

Follow the example image:

The above image is what I'm trying to make work, notice that we have two first ads have the field (advertising = 2) with banner, and the last three without banner have the field (advertising = 1). I need the first two that have the field (advertising = 2) to be the first to be displayed (but with each update of the page these two ads randomize). The ads that have the remaining field (publicity = 1) also randomize.

sql

asked by anonymous 30.04.2018 / 15:59

2 answers

if / else condition with elseif [duplicate] Join of two sql commands

score -1 · Answer 1

Good afternoon, When using RAND () the number will be the same for all rows.

So I understand the goal is to ensure that "published" ads with a value of 2 always come first. Assuming that the degree of importance of the ads stays in this way (3 will appear first that 2, if it exists), the correct way to do it is:

 SELECT NewID() rnd, id, slug FROM anuncios
ORDER BY publicado DESC, rnd

score -2 · Answer 2

Hello, what you will have to do is separate this into 2 queries, generate a single element, and then use that element to do the order by but without selecting it. So:

CREATE TABLE Foo (
    id BIGINT NOT NULL IDENTITY(1,1),
    nome NVARCHAR(100) NOT NULL
);

INSERT INTO Foo (nome) VALUES 
    ('João'), ('Maria'), ('José'), ('Antônio'), ('André'), 
    ('Lucas'), ('Leandro'), ('Samuel'), ('Lorena'), ('Filipe');

SELECT
    id, nome
FROM
    (SELECT id, nome, NEWID() as random FROM Foo) as x
ORDER BY
    random

If you use this way, each row will have a new reference element, and then the sort will resolve without the need for additional field creation ...

I hope it helps.