From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Arup Rakshit <aruprakshit(at)rocketmail(dot)com> |
Cc: | Pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Random order by but first 3 |
Date: | 2015-06-11 17:09:56 |
Message-ID: | CAKFQuwa86jcG_p961VWbWNbt5Pc0LRRUfGoUesSbP2JFYApuqw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 11, 2015 at 12:35 PM, Arup Rakshit <aruprakshit(at)rocketmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jimmy Yih | 2015-06-11 17:30:30 | Re: GCC error and libmpfr.so.4 not found |
Previous Message | Thom Brown | 2015-06-11 16:55:57 | Re: SET LOCAL synchronous_commit TO OFF |