How do SQL sort and randomize at the same time?

Asked

Viewed 83 times

-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, only that of the above code is only doing the sort, the randomization does not work.

Follow actual image of example:

inserir a descrição da imagem aqui

The image above is what I’m trying to make work, note that we have two first advertisements 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 at each page update these two advertisements randomize). Already the advertisements that have the field (advertising=1) remaining also randomize.

sql
  • 4

    What do you mean? Either you order or you don’t order the data. The most you will get is to randomize a sample by placing a top 10 for example, so you would have 10 records chosen randomly but ordered by publication date.

  • @jean I need SQL to display ordered data because I have a field that has two values (published=1 and published=2) that sorts the published field in the ASC order, but that also Randomize.

  • 1

    Gladison, put a minimal example of how your records are and how you expect the return of select.

  • The clause ORDER BY will only sort by the fields you command, in relation to the other fields there is no ordering which means it will catch an "arbitrary order" (Random) in relation to the other fields but which, in general, will always catch the same arbitrary order.

  • @Jean In this case how do I make Rand work too?

  • You don’t understand yet, the RAND is working and delivering a randomly ordered dataset for sorting by publication date but as this sorting ignores all other fields the other fields will come "cluttered" but will usually come cluttered in the same way

  • Possible duplicate of How to select with ORDER BY and different criteria?

  • 2

    Just a published ORDER BY DESC, RAND() (see question from suggested closing link)

Show 3 more comments

2 answers

-1

Good afternoon, When using RAND() the number will be equal for all lines.

From what I understand the objective is to ensure that "published" ads with the value 2 always appear first. Assuming that the degree of importance of advertisements remains this way (3 will appear before 2, if any), the correct way to do this is to:

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

  • While this may work p/ Mysql or TSQL will not work for Oracle and OP will not "tagged" the SGBD

-2

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

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 solve without the need for additional field creation...

I hope it helps.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.