T-SQL 2012 Apply the DISTINCT Clause to only one field in the Select Statement in a Query

All Replies

• Thursday, March 21, 2013 11:52 PM

   

   
  

  1

  Even If you use group by clause, you have to include all the fields that are in select list. One easy way to do is do a CTE and do a ROW_NUMBER on the keyfield and select only those records where row number= 1

  Example

  ;WITH CTE

  AS

  (

  SELECT Key1

  Field 1,....Field n

  ,ROW_NUMBER() OVER (PARTITION BY Key1 ORDER BY Field ASC/DESC) AS rn

  )

  SELECT * FROM CTE WHERE rn=1

  • Proposed As Answer by Naarasimha Friday, March 22, 2013 12:57 AM
  • Marked As Answer by ConstPM Sunday, March 24, 2013 12:08 AM
  •  
  • Reply
  • Quote

   
• Friday, March 22, 2013 3:56 AM

   

   
  

  0

  Thank you for your response.  This is the query used in my view that

  SELECT

  EventMaster.EventID, --This is a key field 

  EventMaster.EquipNumber

  EquipmentMaster.Co

  EquipMaster.EquipDescription

  FROM EventMaster

  LEFT JOIN EquipMaster ON

    EquipMaster.EquipNumber = EventMaster.EquipNumber

  GO

  Comments:

  This query is giving me multiple records for the same EventID.  The only difference in the records is the EquipMaster.Co values are different.   How would the CTE syntax be introduced into this Select statement?  I assume i would nest my current query inside the CTE and have the CTE determine the duplicate records?

  • Reply
  • Quote

   
• Friday, March 22, 2013 11:18 AM

   

   
  

  0

  This is a sitution where you need to ask yourself what you are doing. Yes, you get multiple rows for the same EventID - that is exactly how you ordered it, Sir.

  The query says "show me all rows in EventMaster, and for every in EventMaster, show me all rows in EquipMaster. So if there are multiple rows in EquipMaster - you don't want to see all? But which one do you want to see?

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
  • Reply
  • Quote

   
• Friday, March 22, 2013 3:05 PM

   

   
  

  0
  When you say eventid is the key...why Are you joining on other field. Is this the requirement?
  

  • Edited by Harsha Lella Friday, March 22, 2013 3:07 PM word
  •  
  • Reply
  • Quote

   
• Sunday, March 24, 2013 12:08 AM

   

   
  

  0

  Erland,

  Please read the question before you respond.  You are seldom on point and typically not helpful.

  • Reply
  • Quote

   
• Sunday, March 24, 2013 11:24 AM

   

   
  

  0

  I read the question, and my response was that you need to take a step back and ask yourself what you are looking for.

  You are not the first one to ask "how I get a DISTINCT for only one column". The problem with this question is that it does not make sense without further specification.

  Say that you have:

  ID             Value
  1                879
  1                344
  1                356
  2                900
  2                111
  2                233

  You run the query

   SELECT DISTINCT ID, Value FROM tbl

  and of course you get six rows back. Your question says "I only want one row per ID". That is of course possible to achieve in a number of ways:

    SELECT DISTINCT ID FROM tbl
    SELECT ID, SUM(Value) FROM tbl GROUP BY ID
    SELECT ID, MIN(Value) FROM tbl GROUP BY ID
    SELECT ID, MAX(Value) FROM tbl GROUP BY ID
    SELECT ID, AVG(Value) FROM tbl GROUP BY Value
    WITH CTE (ID, Value, rowno) AS (
        SELECT ID, Value, row_number() OVER(PARITION BY ID ORDER BY newid())
        FROM   tbl
    )
    SELECT ID, Value
    FROM   tbl
    WHERE  rowno = 1

  The last one makes a truly random choice.

  In your case you get multiple values in EquipmentMaster.Co, but you only want one row per EventID. The question is then: which of the different values in EquipmentMaster.Co do you want to see?

  This is the question you need to answer before you can get a solution. Maybe the answer is "Any, I don't care which". But that is still an answer. If I don't know what your results you are looking for, I can't help you.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
  • Reply
  • Quote