Select distinct rows from duplicate rows....

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sanjeev.atvankar@gmail.com
    #1

    Select distinct rows from duplicate rows....

    Dear Gurus,

    I have table with following entries

    Table name = Customer

    Name Weight
    ------------ -----------
    Sanjeev 85
    Sanjeev 75
    Rajeev 80
    Rajeev 45
    Sandy 35
    Sandy 30
    Harry 15
    Harry 45

    I need a output as follow

    Name Weight
    ------------ -----------
    Sanjeev 85
    Rajeev 80
    Sandy 30
    Harry 45

    OR

    Name Weight
    ------------ -----------
    Sanjeev 75
    Rajeev 45
    Sandy 35
    Harry 15

    i.e. only distinct Name should display with only one value of Weight.
    I tried with 'group by' on Name column but it shows me all rows.

    Could anyone help me for above.

    Thanking in Advance.

    Regards
    Sanjeev
    sanjeev.atvanka r@gmail.com
    Tags: None
    • Jason Lepack
      #2
      Re: Select distinct rows from duplicate rows....

      select
      [Name],
      max([Weight]) as wgt
      from
      [Customer]
      group by
      [Name]

      Cheers,
      Jason Lepack

      On Nov 28, 8:26 am, sanjeev.atvan.. .@gmail.com wrote:
      Dear Gurus,
      >
      I have table with following entries
      >
      Table name = Customer
      >
      Name Weight
      ------------ -----------
      Sanjeev 85
      Sanjeev 75
      Rajeev 80
      Rajeev 45
      Sandy 35
      Sandy 30
      Harry 15
      Harry 45
      >
      I need a output as follow
      >
      Name Weight
      ------------ -----------
      Sanjeev 85
      Rajeev 80
      Sandy 30
      Harry 45
      >
      OR
      >
      Name Weight
      ------------ -----------
      Sanjeev 75
      Rajeev 45
      Sandy 35
      Harry 15
      >
      i.e. only distinct Name should display with only one value of Weight.
      I tried with 'group by' on Name column but it shows me all rows.
      >
      Could anyone help me for above.
      >
      Thanking in Advance.
      >
      Regards
      Sanjeev
      sanjeev.atvan.. .@gmail.com

        Comment

        • Jason Lepack
          #3
          Re: Select distinct rows from duplicate rows....

          Please note that because you don't care which weight you get, max()
          can be replaced with min(), first(), last(), etc.

          Cheers,
          Jason Lepack

          On Nov 28, 8:26 am, sanjeev.atvan.. .@gmail.com wrote:
          Dear Gurus,
          >
          I have table with following entries
          >
          Table name = Customer
          >
          Name Weight
          ------------ -----------
          Sanjeev 85
          Sanjeev 75
          Rajeev 80
          Rajeev 45
          Sandy 35
          Sandy 30
          Harry 15
          Harry 45
          >
          I need a output as follow
          >
          Name Weight
          ------------ -----------
          Sanjeev 85
          Rajeev 80
          Sandy 30
          Harry 45
          >
          OR
          >
          Name Weight
          ------------ -----------
          Sanjeev 75
          Rajeev 45
          Sandy 35
          Harry 15
          >
          i.e. only distinct Name should display with only one value of Weight.
          I tried with 'group by' on Name column but it shows me all rows.
          >
          Could anyone help me for above.
          >
          Thanking in Advance.
          >
          Regards
          Sanjeev
          sanjeev.atvan.. .@gmail.com

            Comment

            • SQL Menace
              #4
              Re: Select distinct rows from duplicate rows....

              SELECT Name, MAX(Weight) AS Weight
              FROM Customer
              GROUP BY Name

              or

              SELECT Name, MIN(Weight) AS Weight
              FROM Customer
              GROUP BY Name

              Denis The SQL Menace
              SQL Server Code,Tips and Tricks, Performance Tuning
              http://sqlservercode.blogspot.com
              A blog about SQL Server, Books, Movies and life in general

              http://sqlblog.com/blogs/denis_gobo/default.aspx




              On Nov 28, 8:26 am, sanjeev.atvan.. .@gmail.com wrote:
              Dear Gurus,
              >
              I have table with following entries
              >
              Table name = Customer
              >
              Name Weight
              ------------ -----------
              Sanjeev 85
              Sanjeev 75
              Rajeev 80
              Rajeev 45
              Sandy 35
              Sandy 30
              Harry 15
              Harry 45
              >
              I need a output as follow
              >
              Name Weight
              ------------ -----------
              Sanjeev 85
              Rajeev 80
              Sandy 30
              Harry 45
              >
              OR
              >
              Name Weight
              ------------ -----------
              Sanjeev 75
              Rajeev 45
              Sandy 35
              Harry 15
              >
              i.e. only distinct Name should display with only one value of Weight.
              I tried with 'group by' on Name column but it shows me all rows.
              >
              Could anyone help me for above.
              >
              Thanking in Advance.
              >
              Regards
              Sanjeev
              sanjeev.atvan.. .@gmail.com

                Comment

                • Madhivanan
                  #5
                  Re: Select distinct rows from duplicate rows....

                  On Nov 28, 6:47 pm, Jason Lepack <jlep...@gmail. comwrote:
                  Please note that because you don't care which weight you get, max()
                  can be replaced with min(), first(), last(), etc.
                  >
                  Cheers,
                  Jason Lepack
                  >
                  On Nov 28, 8:26 am, sanjeev.atvan.. .@gmail.com wrote:
                  >
                  >
                  >
                  Dear Gurus,
                  >
                  I have table with following entries
                  >
                  Table name = Customer
                  >
                  Name Weight
                  ------------ -----------
                  Sanjeev 85
                  Sanjeev 75
                  Rajeev 80
                  Rajeev 45
                  Sandy 35
                  Sandy 30
                  Harry 15
                  Harry 45
                  >
                  I need a output as follow
                  >
                  Name Weight
                  ------------ -----------
                  Sanjeev 85
                  Rajeev 80
                  Sandy 30
                  Harry 45
                  >
                  OR
                  >
                  Name Weight
                  ------------ -----------
                  Sanjeev 75
                  Rajeev 45
                  Sandy 35
                  Harry 15
                  >
                  i.e. only distinct Name should display with only one value of Weight.
                  I tried with 'group by' on Name column but it shows me all rows.
                  >
                  Could anyone help me for above.
                  >
                  Thanking in Advance.
                  >
                  Regards
                  Sanjeev
                  sanjeev.atvan.. .@gmail.com- Hide quoted text -
                  >
                  - Show quoted text -
                  Note that SQL Server doesnt support first() or last() function

                    Comment

                    Previous template Next
                    Working...