Home Forums SQL Server 2012 SQL Server 2012 - T-SQL What's wrong with my DISTINCT RE: What's wrong with my DISTINCT

  • February 5, 2013 at 11:38 am

    #1584543

    momba (2/5/2013)

    I've got another noob question...

    When I run this query to just grab the emp_id I get 6474 but when I request more fields I get 6605?

    select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id

    , CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date

    , ADMISSION_CDE

    , ADMIT_TYPE_CDE

    , CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date

    , DISC_CDE

    , DISC_TYPE_CDE

    from CurrentTable

    WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))

    order by emp_id , adm_date

    ...I just want the record for the most recent adm_date what's up with my distinct?

    The problem is with your understanding of the DISTINCT operator. It returns all distinct records based on all the columns in the select list. Each row will be distinct.