Post reply

select distinct in a sub query

  • January 26, 2011 at 6:24 am

    #234020

    Hi

    I have the following script. The main body give me the correct answer but i have more than one row per patient.

    I need the result to give me a distinct list on the UKEY field

    I have added a #temp table and done it that way but is there a way of incorporating it into the main script????????????????????????????????

    Thanks in advance

    SELECT tbl_PS_IXP_MEDREC_PROCS.crn +':' + tbl_PS_IXP_MEDREC_PROCS.admission AS UKEY, tbl_PS_IXP_MEDREC_PROCS.admdate_dte,

    tbl_PS_IXP_MEDREC_PROCS.ADM_YEAR, tbl_PS_IXP_MEDREC_PROCS.ADM_MONTH, tbl_PS_IXP_MEDREC_PROCS.ADM_MONTH_NUMBER,

    tbl_PS_IXP_MEDREC_PROCS.disdate_dte, tbl_PS_IXP_MEDREC_PROCS.DIS_YEAR, tbl_PS_IXP_MEDREC_PROCS.DIS_MONTH,

    tbl_PS_IXP_MEDREC_PROCS.DIS_MONTH_NUMBER, tbl_PS_IXP_MEDREC_PROCS.[med rec epi],

    tbl_PS_IXP_MEDREC_PROCS.EPISODE_START_DATE, tbl_PS_IXP_MEDREC_PROCS.EPISODE_END_DATE, tbl_PS_IXP_MEDREC_PROCS.opcscode,

    tbl_PS_IXP_MEDREC_DIAG.ICD_POSITION, tbl_PS_IXP_MEDREC_PROCS.OPCS_Desc, tbl_PS_IXP_MEDREC_PROCS.[PROC POSITION],

    tbl_PS_IXP_MEDREC_PROCS.hrgcode, tbl_PS_IXP_MEDREC_PROCS.HRG_DESC, tbl_PS_IXP_MEDREC_PROCS.[SPELL LOS],

    tbl_PS_IXP_MEDREC_PROCS.intmgt, tbl_PS_IXP_MEDREC_PROCS.INTMGT_DESC, tbl_PS_IXP_MEDREC_PROCS.admtype,

    tbl_PS_IXP_MEDREC_PROCS.ADM_TYPE_DESC, tbl_PS_IXP_MEDREC_PROCS.operationdate_dte, tbl_PS_IXP_MEDREC_PROCS.surgeon,

    tbl_PS_IXP_MEDREC_PROCS.SURGEON_SURN, tbl_PS_IXP_MEDREC_PROCS.specialty, tbl_PS_IXP_MEDREC_PROCS.SPEC_DESC,

    tbl_PS_IXP_MEDREC_PROCS.consultant, tbl_PS_IXP_MEDREC_PROCS.CONS_SURN, tbl_PS_IXP_MEDREC_PROCS.discode,

    tbl_PS_IXP_MEDREC_DIAG.ICD_DESC, tbl_PS_IXP_MEDREC_DIAG.icdcode, R_FD_BasketOf25_Daycases.Basket_of_25

    into #pstemp

    FROM tbl_PS_IXP_MEDREC_PROCS INNER JOIN

    tbl_PS_IXP_MEDREC_DIAG ON tbl_PS_IXP_MEDREC_PROCS.crn = tbl_PS_IXP_MEDREC_DIAG.crn AND

    tbl_PS_IXP_MEDREC_PROCS.admission = tbl_PS_IXP_MEDREC_DIAG.admission AND

    tbl_PS_IXP_MEDREC_PROCS.[med rec epi] = tbl_PS_IXP_MEDREC_DIAG.[med rec epi] INNER JOIN

    R_FD_BasketOf25_Daycases ON tbl_PS_IXP_MEDREC_PROCS.opcscode = R_FD_BasketOf25_Daycases.OPCS_Code

    WHERE (tbl_PS_IXP_MEDREC_PROCS.opcscode BETWEEN 'W82%' AND 'W89%') AND (tbl_PS_IXP_MEDREC_DIAG.ICD_POSITION = 1) AND

    (tbl_PS_IXP_MEDREC_PROCS.EPISODE_END_DATE between '2010-04-01' and '2010-04-30')and

    tbl_PS_IXP_MEDREC_PROCS.[SPELL LOS] = 0

    ----------------------------------------

    -----------------------------------------

    select distinct UKEY

    from #pstemp

    order by #pstemp.ukey

    drop table #pstemp

  • January 26, 2011 at 7:28 am

    #1278463

    If you only need the list of UKEY, why are you selecting all the other columns?

    Not having all your code to understand completely what's going on, I may get this wrong.

    What I'd do, based on what you've described and shown, is use this as a derived table, not a sub-select. As a derived table you can join against it with the tables in the rest of the query to get what you're looking for.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 26, 2011 at 7:41 am

    #1278470

    Without seeing table definitions, I can only suggest:

    a)If you are getting duplicates, then you are probably missing a condition on one of your joins.

    b)If any of your tables allow one to many relationships, then you need to refine your query.

  • January 26, 2011 at 7:49 am

    #1278482

    I have sorted this now, thanks for you advice.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply