none
Select entire record with distinct on a specific field RRS feed

  • Question

  • How would I write the following SQL statement:
    SELECT DISTINCT(Field1), Field2, Field3, Field4 FROM Table

    All of the examples of Distinct that I have seen for LINQ are only for if you are selecting a single field.  I need to select all the fields in a table and have it be distinct on a specific field.

    Thanks for any help.
    May your code never smell
    Wednesday, January 21, 2009 4:34 PM

Answers

  • JasonFr said:

    How would I write the following SQL statement:
    SELECT DISTINCT(Field1), Field2, Field3, Field4 FROM Table

    All of the examples of Distinct that I have seen for LINQ are only for if you are selecting a single field.  I need to select all the fields in a table and have it be distinct on a specific field.

    Thanks for any help.


    May your code never smell



    Your sample SQL query will do distinct on all fields.

    I think what you are maybe looking for is groupings+aggregates, e.g. "select field1, min(field2), min(field3), min(field4) from table group by field1". In linq that can be expressed as:

    from tb in dc.Table  
    group tb by tb.Field1 into tbg  
    select new
    field1 = tbg.Key,
      field2 = tbg.Min(f2 => f2.Field2),
      field3 = tbg.Min(f3 => f3.Field3),
      field4 = tbg.Min(f4 => f4.Field4)

    • Marked as answer by JasonFr Thursday, January 22, 2009 1:52 PM
    Thursday, January 22, 2009 2:53 AM
    Answerer

All replies

  • JasonFr said:

    How would I write the following SQL statement:
    SELECT DISTINCT(Field1), Field2, Field3, Field4 FROM Table

    All of the examples of Distinct that I have seen for LINQ are only for if you are selecting a single field.  I need to select all the fields in a table and have it be distinct on a specific field.

    Thanks for any help.


    May your code never smell



    Your sample SQL query will do distinct on all fields.

    I think what you are maybe looking for is groupings+aggregates, e.g. "select field1, min(field2), min(field3), min(field4) from table group by field1". In linq that can be expressed as:

    from tb in dc.Table  
    group tb by tb.Field1 into tbg  
    select new
    field1 = tbg.Key,
      field2 = tbg.Min(f2 => f2.Field2),
      field3 = tbg.Min(f3 => f3.Field3),
      field4 = tbg.Min(f4 => f4.Field4)

    • Marked as answer by JasonFr Thursday, January 22, 2009 1:52 PM
    Thursday, January 22, 2009 2:53 AM
    Answerer
  • Yes, you are correct KristoferA.  I realized the error in my sample query after I posted the original question and came to the realization that I was looking for a group by with aggregates as you mentioned.  Thanks for the sample LINQ statement to point me in that direction!
    May your code never smell
    Thursday, January 22, 2009 1:52 PM