Hi there ans thx for reading and answering this post if you can: Let's regard an example of a table (stars) containing a code for a star (symb) and its index (nb) in a file.
nb | symb ----+------------ 0 | alTau 1 | alTau 2 | bePer 3 | alSco 4 | alLeo 5 | alCMa 6 | alVir 7 | Trappist-1 8 | SgrA* 9 | SgrA* 10 | GA 11 | VC 12 | M31 13 | M44 14 | alUMi 15 | M87 16 | alCyg 17 | alCyg 18 | beOri 19 | omiCet 20 | epTau 21 | epCas 22 | alAnd 23 | alAnd 24 | beAnd 25 | ga-1And 26 | ga-1And 27 | ga-1And 28 | ga-1And 29 | xiAnd 30 | upAnd 31 | upAnd 32 | 14And 33 | 51And 34 | M31 35 | alAql 36 | alAql 37 | beAql 38 | gaAql 39 | deAql 40 | epAql 41 | zeAql 42 | zeAql 43 | etAql 44 | thAql 45 | ioAql 46 | laAql 47 | xiAql 48 | 12Aql 49 | alAqr 50 | alAqr 51 | beAqr 52 | gaAqr 53 | gaAqr 54 | deAqr 55 | epAqr 56 | epAqr 57 | ze-1Aqr 58 | etAqr 59 | etAqr 60 | thAqr 61 | kaAqr 62 | laAqr 63 | laAqr 64 | laAqr 65 | laAqr 66 | nuAqr 67 | piAqr 68 | xiAqr 69 | Trappist-1 70 | alAra 71 | muAra 72 | alAri 73 | beAri 74 | beAri 75 | gaAri 76 | deAri 77 | 39Ari 78 | 41Ari 79 | alAur 80 | alAur 81 | beAur 82 | deAur 83 | epAur 84 | epAur 85 | epAur 86 | zeAur 87 | zeAur 88 | zeAur 89 | zeAur 90 | etAur 91 | etAur 92 | thAur 93 | thAur 94 | thAur 95 | ioAur 96 | ioAur 97 | ioAur 98 | alBoo 99 | alBoo Notice that some symbs are duplicated and I want to select only the distinct symbs. So, I could use select distinct on(symb) * from stars; I get this result: nb | symb ----+------- 48 | 12Aql 32 | 14And 77 | 39Ari 78 | 41Ari 33 | 51And 10 | GA 34 | M31 13 | M44 15 | M87 9 | SgrA* and so on... But when I want to reorder it by nb with: with x as (select distinct on(symb) * from stars) select * from x order by nb; nb | symb ----+------------ 1 | alTau 2 | bePer 3 | alSco 4 | alLeo 5 | alCMa 6 | alVir 7 | Trappist-1 9 | SgrA* 10 | GA 11 | VC 13 | M44 14 | alUMi 15 | M87 16 | alCyg 18 | beOri 19 | omiCet 20 | epTau 21 | epCas 23 | alAnd 24 | beAnd 28 | ga-1And ... Notice that 12 is missing in the list. Even with x as (select distinct on(symb) * from stars) select * from x where nb = 12 order by nb; returns nothing, whereas it should, but with x as (select distinct on(symb) * from stars) select * from x where symb = 'M31' order by nb; returns nb | symb ----+------ 12 | M31 which is correct. What should I do in order to get the wanted results. This phenomenon occurs with or without index. On postgres 14.7 linux x86_64. Thx again.
