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.atvankar@gmail.com

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

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

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
http://sqlservercode.blogspot.com
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

Re: Select distinct rows from duplicate rows....

On Nov 28, 6:47 pm, Jason Lepack wrote:
> 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