Re: Random order by but first 3

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Random order by but first 3
Дата
Msg-id CAKFQuwa86jcG_p961VWbWNbt5Pc0LRRUfGoUesSbP2JFYApuqw@mail.gmail.com
обсуждение исходный текст
Ответ на Random order by but first 3  (Arup Rakshit <aruprakshit@rocketmail.com>)
Список pgsql-general
Дерево обсуждения
On Thu, Jun 11, 2015 at 12:35 PM, Arup Rakshit <aruprakshit@rocketmail.com> wrote:
Hi,

Suppose I have a column t1 for a table. Now t1 holds some numerice value for each row. Say R1 to R5 records has values for the column t1 as :

t1(2,5,8,10,32)

I want the result to be printed as (10, 32, 8, 2, 5) means - Big, Biggest, small , <then any random order>


​
SELECT v FROM (
SELECT *, 
CASE 
when row_number = 2 THEN 1.0 
WHEN row_number = 1 THEN 2.0 
WHEN row_number = 3 THEN 3.0
ELSE 10.0 + random()
END AS custom_rank
FROM (
SELECT *, row_number() OVER (ORDER BY v DESC)
FROM ( VALUES (1),(2),(3),(4),(5) ) val (v)
) v_rank
) custom_rank
ORDER BY custom_rank

Feel free to mix in different window functions to deal with ties and the like.

David J.​

В списке pgsql-general по дате отправления:

Предыдущее
От: Thom Brown
Дата:
Сообщение: Re: SET LOCAL synchronous_commit TO OFF
Следующее
От: Jimmy Yih
Дата:
Сообщение: Re: GCC error and libmpfr.so.4 not found