Select distinct one column oracle

Question

this is my query

SELECT PHMR_ID,
  NVOL_MVT_COD,
  TO_CHAR(DATE_PREVUE, 'DD/MM/YYYY')     AS DATE_PREVUE,
  TO_CHAR(DATE_ORIGINE_LT, 'DD/MM/YYYY') AS DATE_ORIGINE_LT,
  HTAD,
  DHC_AFF,
  PKG,
  PKG_GEO,
  GATE,
  TTB,
  BQE,
  SALLE,
  ESCALE,
  TA_CLE,
  IM_IMMAT,
  VOL_NB_PAX_GLOB,
  VOL_CRIT_DOUANIER,
  VOL_PROTOCOLE,
  VOL_CORRESP,
  VOL_ETAT_VOL,
  NVL(TAS_GLOB, TER_COD1)                      AS TAS_GLOB,
  NVL(TER_TRAITEMENT, NVL(TAS_GLOB, TER_COD1)) AS TER_TRAITEMENT,
  NUM_SIEGE,
  VOL_ROT,
  INFO_SUP,
  TYPE_PREMIER_MSG,
  PREMIER_MSG,
  TYPE_DERNIER_MSG,
  DERNIER_MSG,
  TYP_JOUR,
  PHMR_ID_ARP,
  DECODE(NVOL_MVT_COD,'A',PHMR_ID_ARP,NULL) AS ID_ARP_A,
  DECODE(NVOL_MVT_COD,'D',PHMR_ID_ARP,NULL) AS ID_ARP_D,
  TYP_HORAIRE,
  DUREE_CORRESP,
  HOTEL,
  TYPE_CONTACT,
  RET_COD1,
  RET_LIB1,
  RET_COD2,
  RET_LIB2,
  HBLOC,
  DISTINCT_LOT_CORRESPONDANCE,
  OTHER_TYPE_MR,
  TYPE_CHAISE,
  STATUS_PHMR,
  LISTE_MSG,
  LIEU_RDV,
  CLASSE,
  PHMR_PRESTA_ID,
  PRESTA,
  DECODE(HORS_DELAI,1,'OUI','NON') AS HORSDELAI
FROM VW_PHMR
WHERE VW_PHMR.DATE_PREVUE >= to_date('06/07/2015','DD/MM/YYYY')
AND VW_PHMR.DATE_PREVUE   <= to_date('06/07/2015','DD/MM/YYYY')
AND (phmr_valide          IS NULL
OR phmr_valide             ='O')
ORDER BY VW_PHMR.DATE_PREVUE ASC,
  DHC ASC,
  PHMR_ID ASC

I need to get only distinct PHMR_ID (only disticnt on this column), how can I do it?

To be clear; I have a view: CREATE OR REPLACE FORCE VIEW VW_PHMR ( PHMR_ID, TER_COD1, PHMR_NOM, PHMR_PRENOM, TYPE_MR, CIE_SARIA, CIE_COD_IATA, CIE_COD_OACI, NVOL_LIG_NUM, NVOL_MVT_COD, DATE_PREVUE, DATE_ORIGINE_LT, DHC, HTAD, DHC_AFF, PKG, PKG_GEO, GATE, TTB, BQE, SALLE, ESCALE, TA_CLE, IM_IMMAT, VOL_NB_PAX_GLOB, VOL_CRIT_DOUANIER, VOL_PROTOCOLE, VOL_CORRESP, VOL_ETAT_VOL, TAS_GLOB, TER_TRAITEMENT, NUM_SIEGE, VOL_ROT, INFO_SUP, PREMIER_MSG, TYPE_PREMIER_MSG, DERNIER_MSG, TYPE_DERNIER_MSG, TYP_JOUR, PHMR_ID_ADP, TYP_HORAIRE, DUREE_CORRESP, HOTEL, TYPE_CONTACT, RET_COD1, RET_LIB1, RET_COD2, RET_LIB2, HBLOC, DISTINCT_LOT_CORRESPONDANCE, OTHER_TYPE_MR, TYPE_CHAISE, STATUS_PHMR, ID_STATUS, LISTE_MSG, LIEU_RDV, CLASSE, PHMR_VALIDE, PHMR_PRESTA_ID, PRESTA, HORS_DELAI ) AS SELECT DISTINCT p.phmr_id, p.ter_cod1, p.phmr_nom, p.phmr_prenom, p.liste_mr AS type_mr, p.cie_cle AS cie_saria, c.cie_cod_iata, c.cie_cod_oaci, p.nvol_lig_num, p.nvol_mvt_cod, p.date_exp AS date_prevue, p.vol_dt AS date_origine_lt, v.dhc AS DHC, TO_CHAR (v.HTAD, 'HH24:MI') AS HTAD, DECODE ( v.vol_hadb, NULL, v.DHC_TYP || TO_CHAR (v.dhc, 'HH24:MI'), DECODE (v.dhc_typ, 'N', 'D', v.dhc_typ) || TO_CHAR (v.vol_hadb, 'HH24:MI')) AS DHC_AFF, v.parking AS PKG, v.pkg_geo AS PKG_GEO, v.porte AS GATE, v.tapis AS TTB, v.banque AS BQE, v.salle AS SALLE, v.escale AS ESCALE, v.ta_cle, v.im_immat, v.vol_nb_pax_glob, v.vol_crit_douanier, v.protocole AS VOL_PROTOCOLE, p.vol_corresp, v.vol_etat_vol, v.tas_glob, v.tas_af_pax AS TER_TRAITEMENT, p.num_siege, v2.cie_cle || v2.nvol_lig_num AS vol_rot, p.INFO_SUP AS INFO_SUP, TO_CHAR (p.DH_PREMIER_MSG, 'DD/MM/YYYY HH24:MI') AS premier_msg, tm.typmes_cod AS type_premier_msg, TO_CHAR (p.DH_DERNIER_MSG, 'DD/MM/YYYY HH24:MI') AS dernier_msg, tm2.typmes_cod AS type_dernier_msg, DECODE (p.TYP_JOUR, NULL, 'S', p.TYP_JOUR), p.PHMR_ID_ADP, DECODE (p.TYP_HORAIRE, NULL, 'J', p.TYP_HORAIRE), p.DUREE_CORRESP, p.HOTEL, v.TYPE_CONTACT, v.RET_COD1, v.RET_LIB1, v.RET_COD2, v.RET_LIB2, v.vol_hadb, p.DISTINCT_LOT_CORRESPONDANCE, p.TYPE_MR, P.TYPE_CHAISE, sp.status_lib_court AS STATUS_PHMR, sp.PHMR_STATUS AS ID_STATUS, p.liste_message AS liste_msg, p.phmr_lieupc AS lieu_rdv, p.phmr_classe AS classe, p.phmr_valide AS phmr_valide, p.phmr_presta_id, p.presta, p.HORS_DELAI FROM phmr p, cie c, vw_vols v, vw_vols v2, type_messages tm, type_messages tm2, status_phmr sp WHERE c.cie_cle(+) = p.cie_cle AND v.vol_ident(+) = p.vol_ident AND v2.vol_ident(+) = v.vol_ident_rot AND tm.msg_typ(+) = p.TYP_PREMIER_MSG AND tm2.msg_typ(+) = p.TYP_DERNIER_MSG AND sp.phmr_status(+) = p.phmr_status;

when i Execute this command on this view i Have 1904 rows

Now, I need to have a left join to get another column ( I MUST HAVE ALWAYS 1904 ROWs), so I modify my view like this CREATE OR REPLACE FORCE VIEW VW_PHMR ( PHMR_ID, TER_COD1, PHMR_NOM, PHMR_PRENOM, TYPE_MR, CIE_SARIA, CIE_COD_IATA, CIE_COD_OACI, NVOL_LIG_NUM, NVOL_MVT_COD, DATE_PREVUE, DATE_ORIGINE_LT, DHC, HTAD, DHC_AFF, PKG, PKG_GEO, GATE, TTB, BQE, SALLE, ESCALE, TA_CLE, IM_IMMAT, VOL_NB_PAX_GLOB, VOL_CRIT_DOUANIER, VOL_PROTOCOLE, VOL_CORRESP, VOL_ETAT_VOL, TAS_GLOB, TER_TRAITEMENT, NUM_SIEGE, VOL_ROT, INFO_SUP, PREMIER_MSG, TYPE_PREMIER_MSG, DERNIER_MSG, TYPE_DERNIER_MSG, TYP_JOUR, PHMR_ID_ADP, TYP_HORAIRE, DUREE_CORRESP, HOTEL, TYPE_CONTACT, RET_COD1, RET_LIB1, RET_COD2, RET_LIB2, HBLOC, DISTINCT_LOT_CORRESPONDANCE, OTHER_TYPE_MR, TYPE_CHAISE, DERNIER_JALON, STATUS_PHMR, ID_STATUS, LISTE_MSG, LIEU_RDV, CLASSE, PHMR_VALIDE, PHMR_PRESTA_ID, PRESTA, HORS_DELAI ) AS SELECT DISTINCT p.phmr_id, p.ter_cod1, p.phmr_nom, p.phmr_prenom, p.liste_mr AS type_mr, p.cie_cle AS cie_saria, c.cie_cod_iata, c.cie_cod_oaci, p.nvol_lig_num, p.nvol_mvt_cod, p.date_exp AS date_prevue, p.vol_dt AS date_origine_lt, v.dhc AS DHC, TO_CHAR (v.HTAD, 'HH24:MI') AS HTAD, DECODE ( v.vol_hadb, NULL, v.DHC_TYP || TO_CHAR (v.dhc, 'HH24:MI'), DECODE (v.dhc_typ, 'N', 'D', v.dhc_typ) || TO_CHAR (v.vol_hadb, 'HH24:MI')) AS DHC_AFF, v.parking AS PKG, v.pkg_geo AS PKG_GEO, v.porte AS GATE, v.tapis AS TTB, v.banque AS BQE, v.salle AS SALLE, v.escale AS ESCALE, v.ta_cle, v.im_immat, v.vol_nb_pax_glob, v.vol_crit_douanier, v.protocole AS VOL_PROTOCOLE, p.vol_corresp, v.vol_etat_vol, v.tas_glob, v.tas_af_pax AS TER_TRAITEMENT, p.num_siege, v2.cie_cle || v2.nvol_lig_num AS vol_rot, p.INFO_SUP AS INFO_SUP, TO_CHAR (p.DH_PREMIER_MSG, 'DD/MM/YYYY HH24:MI') AS premier_msg, tm.typmes_cod AS type_premier_msg, TO_CHAR (p.DH_DERNIER_MSG, 'DD/MM/YYYY HH24:MI') AS dernier_msg, tm2.typmes_cod AS type_dernier_msg, DECODE (p.TYP_JOUR, NULL, 'S', p.TYP_JOUR), p.PHMR_ID_ADP, DECODE (p.TYP_HORAIRE, NULL, 'J', p.TYP_HORAIRE), p.DUREE_CORRESP, p.HOTEL, v.TYPE_CONTACT, v.RET_COD1, v.RET_LIB1, v.RET_COD2, v.RET_LIB2, v.vol_hadb, p.DISTINCT_LOT_CORRESPONDANCE, p.TYPE_MR, P.TYPE_CHAISE, **p2.TYP_DERNIER_JALON AS DERNIER_JALON**, sp.status_lib_court AS STATUS_PHMR, sp.PHMR_STATUS AS ID_STATUS, p.liste_message AS liste_msg, p.phmr_lieupc AS lieu_rdv, p.phmr_classe AS classe, p.phmr_valide AS phmr_valide, p.phmr_presta_id, p.presta, p.HORS_DELAI FROM phmr p, **phmr_presta p2**, cie c, vw_vols v, vw_vols v2, type_messages tm, type_messages tm2, status_phmr sp WHERE c.cie_cle(+) = p.cie_cle AND v.vol_ident(+) = p.vol_ident AND v2.vol_ident(+) = v.vol_ident_rot AND tm.msg_typ(+) = p.TYP_PREMIER_MSG AND tm2.msg_typ(+) = p.TYP_DERNIER_MSG AND sp.phmr_status(+) = p.phmr_status **AND p.phmr_id(+) = p2.phmr_id**;

How can I resolve this?

Regards

You have to decide what values would be returned for the other columns. `Min`? `Max`? `sum`?, `Count` or other aggregate functions. Otherwise you can't just get a "Distinct." You could write a separate query to just return distinct values of that column however... Show some data and expected results and we may be able to figure out a different method (or write an SQLFIDDLE.com) — xQbert, Jul 20 '15 at 13:44
Based on your Comment/Answer... the problem you have is that there is a 1-many relationship between `phmr` and `phmr_presta`. Since you only seem to need `TYP_DERNIER_JALON` from `phmr_presta` table... what values do you need? the oldest? newest? all of them? the 1-many needs to be handled to retain your record count of 1904 Again sample data would help here to show that phmr.phmr_ID = 1 and phmr_presta.phmr_id has multiple 1s... knowing which one you're after would help. This is NOT a distinct issue it's likely a SET issue. You need to limit the phmr_presta tables data set before join — xQbert, Jul 20 '15 at 14:26
Yes is a relation 1-many between phmr and phmr_presta so I need the newest TYP_DERNIER_JALON from phmr_presta. How can i limit the phmr_presta tables data set before join? — user1428798, Jul 20 '15 at 14:45
You'll likely need to write a sub query for phmr_presta to reduce the set down to only those max records for each phmr_Id. If you identify what field in phmr_presta is a data/time data type (or define how you determine "newest") I'm sure someone can help. For example: http://stackoverflow.com/questions/12378424/how-to-get-the-last-row-of-an-oracle-a-table or perhaps: http://stackoverflow.com/questions/11128194/oracle-select-most-recent-date-record. Either of these would subset the base table to include only the most recent record. Then join to that set (aliased subquery) — xQbert, Jul 20 '15 at 14:48
the newest is the row in the table phmr_presta with max phmr_presta_id — user1428798, Jul 20 '15 at 14:51

xQbert · Accepted Answer · 2015-07-20T19:38:15.330

I think if you replace:

 FROM phmr p,
      **phmr_presta p2**,

With

FROM phmr p,
(SELECT IP1.PHMR_ID, IP1.TYP_DERNIER_JALON
FROM phmr_Presta IP1,
       (SELECT phmr_id, max(phmr_presta_id) M_PRESTA_ID
        FROM   phmr_presta
        GROUP BY phmr_id
       ) IP2
where IP1.PHMR_ID = IP2.PHMR_ID
  and IP1.PHMR_PRESTA_ID = IP2.M_PRESTA_ID) P2

you'll get the desired result.

What this does is obtain the max_PHMR_PRESETA_ID for each phmr_ID in phmr_presta. It then joins back to PHMR_PRESTA to get the TYP_DERNIER_JALON you're after. I then substitute this for the table you have as this is a subset of that table.

There may be a far more efficient way of writing this entire view. But I focused on the specific issue you were having, not the whole view. Oh... and you shouldn't need distinct after this...

If you create a fiddle with sample data, I could test this out, but I'm not inclined to do so without data and mockup you provide. So this is UNTESTED and may have some syntax errors.

Note for future questions: Eliminate all the extra columns etc. Break the problem down to it's simplest form and ask that as the question. The extra columns and tables here were "noise" making this a bit much to think though; and quite unnecessary.

thanks a lot for you answer and your advice. I Think that the syntax is incorrect !! — user1428798, Jul 20 '15 at 15:42

score 0 · Answer 2 · answered Jul 20 '15 at 13:52

0

This is how you can do it. If you want something else, then do not forget to write a better specification.

select distinct PHMR_ID
from VW_PHMR

answered Jul 20 '15 at 13:52

Lajos Arpad

64,414
37
100
175

I explain my need in a another comment – user1428798 Jul 20 '15 at 14:13

Select distinct one column oracle

2 Answers2