top question

**I_AM_DON_AND_YOU?** · Jul 20 '05, 01:50 AM

Re: top question

How about this:

Select top 2 name, age
from temptable
group by name, age

"alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
message news:l.10665375 27.1436706542@[63.127.215.130]...[color=blue]
> table(name,age)
>
> need to find top 2 that are distinct by age.
> error check: if two names are the same go to next top age).
>
> table(name,age)
> A 17
> A 17 <<error
> B 14
> C 16
> D 16
>
> got a table like that and
> select distinct top 2 age from table, returns:
>
> A 17
> A 17
>
>
>
>
>
>
> --
> Sent by 3 from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com[/color]

**I_AM_DON_AND_YOU?** · Jul 20 '05, 01:50 AM

Re: top question

You can also do it as:

SELECT DISTINCT TOP 2 Name, Age FROM YourTable

"alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
message news:l.10665375 27.1436706542@[63.127.215.130]...[color=blue]
> table(name,age)
>
> need to find top 2 that are distinct by age.
> error check: if two names are the same go to next top age).
>
> table(name,age)
> A 17
> A 17 <<error
> B 14
> C 16
> D 16
>
> got a table like that and
> select distinct top 2 age from table, returns:
>
> A 17
> A 17
>
>
>
>
>
>
> --
> Sent by 3 from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com[/color]

**Shervin Shapourian** · Jul 20 '05, 01:50 AM

Re: top question

If you only want to eliminate duplicate records, you can use DISTINCT:

select distinct top 2 Name, Age

from YourTable

which returns:

A 17

B 14

But if you want to find two oldest people you have to return 3 records,
because B and C are the same age. In this case you can use WITH TIES:

select distinct top 2 with ties Name, Age

from YourTable

which returns:

A 17

C 16

D 16

Shervin

"alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
message news:l.10665375 27.1436706542@[63.127.215.130]...[color=blue]
> table(name,age)
>
> need to find top 2 that are distinct by age.
> error check: if two names are the same go to next top age).
>
> table(name,age)
> A 17
> A 17 <<error
> B 14
> C 16
> D 16
>
> got a table like that and
> select distinct top 2 age from table, returns:
>
> A 17
> A 17
>
>
>
>
>
>
> --
> Sent by 3 from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com[/color]

**I_AM_DON_AND_YOU?** · Jul 20 '05, 01:50 AM

Re: top question

The .."WITH TIES" statement you wrote wouldn't return the result you wrote.

In this case:

select distinct top 2 with ties Name, Age
from YourTable

WILL NOT RETURN

A 17
C 16
D 16

(also order by is required when you use WITH TIES )

I think WITH TIES return when (n+1), (n+2),..... rows are EXACTLY similar to
n(th) row (when you sepcify TOP n). Here in this case

2nd row ---> C 16
3rd row ---> D 16

aren't EXACTLY same.

"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:O4pkb.3855 $i92.2803@clgrp s13...[color=blue]
> If you only want to eliminate duplicate records, you can use DISTINCT:
>
>
>
> select distinct top 2 Name, Age
>
> from YourTable
>
>
>
> which returns:
>
>
>
> A 17
>
> B 14
>
>
>
> But if you want to find two oldest people you have to return 3 records,
> because B and C are the same age. In this case you can use WITH TIES:
>
>
>
> select distinct top 2 with ties Name, Age
>
> from YourTable
>
>
>
> which returns:
>
>
>
> A 17
>
> C 16
>
> D 16
>
>
>
> Shervin
>
>
> "alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
> message news:l.10665375 27.1436706542@[63.127.215.130]...[color=green]
> > table(name,age)
> >
> > need to find top 2 that are distinct by age.
> > error check: if two names are the same go to next top age).
> >
> > table(name,age)
> > A 17
> > A 17 <<error
> > B 14
> > C 16
> > D 16
> >
> > got a table like that and
> > select distinct top 2 age from table, returns:
> >
> > A 17
> > A 17
> >
> >
> >
> >
> >
> >
> > --
> > Sent by 3 from yahoo subdomain of com
> > This is a spam protected message. Please answer with reference header.
> > Posted via http://www.usenet-replayer.com[/color]
>
>[/color]

**Shervin Shapourian** · Jul 20 '05, 01:50 AM

Re: top question

Oops! Yes you are right, I forgot to add ORDER BY. Use this:

select distinct top 2 with ties Name, Age
from YourTable
order by Age

Now the result is:

A 17
C 16
D 16

In WITH TIES records need to have EXACTLY the same values for the fields
that are listed in ORDER BY clause, not all the fields. In this example C
and D have same value for Age.

Shervin

"I_AM_DON_AND_Y OU?" <user@domain.co m> wrote in message
news:eXsHQHglDH A.1656@tk2msftn gp13.phx.gbl...[color=blue]
> The .."WITH TIES" statement you wrote wouldn't return the result you[/color]
wrote.[color=blue]
>
> In this case:
>
> select distinct top 2 with ties Name, Age
> from YourTable
>
> WILL NOT RETURN
>
> A 17
> C 16
> D 16
>
> (also order by is required when you use WITH TIES )
>
> I think WITH TIES return when (n+1), (n+2),..... rows are EXACTLY similar[/color]
to[color=blue]
> n(th) row (when you sepcify TOP n). Here in this case
>
> 2nd row ---> C 16
> 3rd row ---> D 16
>
> aren't EXACTLY same.
>
>
>
>
> "Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
> news:O4pkb.3855 $i92.2803@clgrp s13...[color=green]
> > If you only want to eliminate duplicate records, you can use DISTINCT:
> >
> >
> >
> > select distinct top 2 Name, Age
> >
> > from YourTable
> >
> >
> >
> > which returns:
> >
> >
> >
> > A 17
> >
> > B 14
> >
> >
> >
> > But if you want to find two oldest people you have to return 3 records,
> > because B and C are the same age. In this case you can use WITH TIES:
> >
> >
> >
> > select distinct top 2 with ties Name, Age
> >
> > from YourTable
> >
> >
> >
> > which returns:
> >
> >
> >
> > A 17
> >
> > C 16
> >
> > D 16
> >
> >
> >
> > Shervin
> >
> >
> > "alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
> > message news:l.10665375 27.1436706542@[63.127.215.130]...[color=darkred]
> > > table(name,age)
> > >
> > > need to find top 2 that are distinct by age.
> > > error check: if two names are the same go to next top age).
> > >
> > > table(name,age)
> > > A 17
> > > A 17 <<error
> > > B 14
> > > C 16
> > > D 16
> > >
> > > got a table like that and
> > > select distinct top 2 age from table, returns:
> > >
> > > A 17
> > > A 17
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Sent by 3 from yahoo subdomain of com
> > > This is a spam protected message. Please answer with reference header.
> > > Posted via http://www.usenet-replayer.com[/color]
> >
> >[/color]
>
>[/color]

**Shervin Shapourian** · Jul 20 '05, 01:50 AM

Re: top question

Oh shoooooot! I really need to run my scripts before posting them!!!
The order by must be descending :-)

select distinct top 2 with ties Name, Age
from YourTable
order by Age desc

Sorry for that.

Shervin

"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:OHpkb.4150 $i92.1865@clgrp s13...[color=blue]
> Oops! Yes you are right, I forgot to add ORDER BY. Use this:
>
> select distinct top 2 with ties Name, Age
> from YourTable
> order by Age
>
> Now the result is:
>
> A 17
> C 16
> D 16
>
> In WITH TIES records need to have EXACTLY the same values for the fields
> that are listed in ORDER BY clause, not all the fields. In this example C
> and D have same value for Age.
>
> Shervin
>
> "I_AM_DON_AND_Y OU?" <user@domain.co m> wrote in message
> news:eXsHQHglDH A.1656@tk2msftn gp13.phx.gbl...[color=green]
> > The .."WITH TIES" statement you wrote wouldn't return the result you[/color]
> wrote.[color=green]
> >
> > In this case:
> >
> > select distinct top 2 with ties Name, Age
> > from YourTable
> >
> > WILL NOT RETURN
> >
> > A 17
> > C 16
> > D 16
> >
> > (also order by is required when you use WITH TIES )
> >
> > I think WITH TIES return when (n+1), (n+2),..... rows are EXACTLY[/color][/color]
similar[color=blue]
> to[color=green]
> > n(th) row (when you sepcify TOP n). Here in this case
> >
> > 2nd row ---> C 16
> > 3rd row ---> D 16
> >
> > aren't EXACTLY same.
> >
> >
> >
> >
> > "Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
> > news:O4pkb.3855 $i92.2803@clgrp s13...[color=darkred]
> > > If you only want to eliminate duplicate records, you can use DISTINCT:
> > >
> > >
> > >
> > > select distinct top 2 Name, Age
> > >
> > > from YourTable
> > >
> > >
> > >
> > > which returns:
> > >
> > >
> > >
> > > A 17
> > >
> > > B 14
> > >
> > >
> > >
> > > But if you want to find two oldest people you have to return 3[/color][/color][/color]
records,[color=blue][color=green][color=darkred]
> > > because B and C are the same age. In this case you can use WITH TIES:
> > >
> > >
> > >
> > > select distinct top 2 with ties Name, Age
> > >
> > > from YourTable
> > >
> > >
> > >
> > > which returns:
> > >
> > >
> > >
> > > A 17
> > >
> > > C 16
> > >
> > > D 16
> > >
> > >
> > >
> > > Shervin
> > >
> > >
> > > "alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > message news:l.10665375 27.1436706542@[63.127.215.130]...
> > > > table(name,age)
> > > >
> > > > need to find top 2 that are distinct by age.
> > > > error check: if two names are the same go to next top age).
> > > >
> > > > table(name,age)
> > > > A 17
> > > > A 17 <<error
> > > > B 14
> > > > C 16
> > > > D 16
> > > >
> > > > got a table like that and
> > > > select distinct top 2 age from table, returns:
> > > >
> > > > A 17
> > > > A 17
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Sent by 3 from yahoo subdomain of com
> > > > This is a spam protected message. Please answer with reference[/color][/color][/color]
header.[color=blue][color=green][color=darkred]
> > > > Posted via http://www.usenet-replayer.com
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**I_AM_DON_AND_YOU?** · Jul 20 '05, 01:50 AM

Re: top question

u r right.

"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:OHpkb.4150 $i92.1865@clgrp s13...[color=blue]
> Oops! Yes you are right, I forgot to add ORDER BY. Use this:
>
> select distinct top 2 with ties Name, Age
> from YourTable
> order by Age
>
> Now the result is:
>
> A 17
> C 16
> D 16
>
> In WITH TIES records need to have EXACTLY the same values for the fields
> that are listed in ORDER BY clause, not all the fields. In this example C
> and D have same value for Age.
>
> Shervin
>
> "I_AM_DON_AND_Y OU?" <user@domain.co m> wrote in message
> news:eXsHQHglDH A.1656@tk2msftn gp13.phx.gbl...[color=green]
> > The .."WITH TIES" statement you wrote wouldn't return the result you[/color]
> wrote.[color=green]
> >
> > In this case:
> >
> > select distinct top 2 with ties Name, Age
> > from YourTable
> >
> > WILL NOT RETURN
> >
> > A 17
> > C 16
> > D 16
> >
> > (also order by is required when you use WITH TIES )
> >
> > I think WITH TIES return when (n+1), (n+2),..... rows are EXACTLY[/color][/color]
similar[color=blue]
> to[color=green]
> > n(th) row (when you sepcify TOP n). Here in this case
> >
> > 2nd row ---> C 16
> > 3rd row ---> D 16
> >
> > aren't EXACTLY same.
> >
> >
> >
> >
> > "Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
> > news:O4pkb.3855 $i92.2803@clgrp s13...[color=darkred]
> > > If you only want to eliminate duplicate records, you can use DISTINCT:
> > >
> > >
> > >
> > > select distinct top 2 Name, Age
> > >
> > > from YourTable
> > >
> > >
> > >
> > > which returns:
> > >
> > >
> > >
> > > A 17
> > >
> > > B 14
> > >
> > >
> > >
> > > But if you want to find two oldest people you have to return 3[/color][/color][/color]
records,[color=blue][color=green][color=darkred]
> > > because B and C are the same age. In this case you can use WITH TIES:
> > >
> > >
> > >
> > > select distinct top 2 with ties Name, Age
> > >
> > > from YourTable
> > >
> > >
> > >
> > > which returns:
> > >
> > >
> > >
> > > A 17
> > >
> > > C 16
> > >
> > > D 16
> > >
> > >
> > >
> > > Shervin
> > >
> > >
> > > "alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > message news:l.10665375 27.1436706542@[63.127.215.130]...
> > > > table(name,age)
> > > >
> > > > need to find top 2 that are distinct by age.
> > > > error check: if two names are the same go to next top age).
> > > >
> > > > table(name,age)
> > > > A 17
> > > > A 17 <<error
> > > > B 14
> > > > C 16
> > > > D 16
> > > >
> > > > got a table like that and
> > > > select distinct top 2 age from table, returns:
> > > >
> > > > A 17
> > > > A 17
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Sent by 3 from yahoo subdomain of com
> > > > This is a spam protected message. Please answer with reference[/color][/color][/color]
header.[color=blue][color=green][color=darkred]
> > > > Posted via http://www.usenet-replayer.com
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

**Vishal Parkar** · Jul 20 '05, 01:50 AM

Re: top question

Alexqa,

Try query in following example

--sample table / records

create table tb (name char(1), age int)
insert into tb
select 'A', 17 union all
select 'A', 17 union all
select 'B', 14 union all
select 'C', 16 union all
select 'D', 16

--query
select name,age from
(select name,age,
(select count(distinct age) from tb b
where a.age <= b.age) rank
from tb a) X
where rank <= 2

--
-Vishal

**I_AM_DON_AND_YOU?** · Jul 20 '05, 01:50 AM

Re: top question

Your query gives the following output:

name age
---- -----------
A 17
A 17
C 16
D 16

But this is not what Alexqa want.

"Vishal Parkar" <_vgparkar@yaho o.co.in> wrote in message
news:%23YqDfygl DHA.2500@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Alexqa,
>
> Try query in following example
>
> --sample table / records
>
> create table tb (name char(1), age int)
> insert into tb
> select 'A', 17 union all
> select 'A', 17 union all
> select 'B', 14 union all
> select 'C', 16 union all
> select 'D', 16
>
> --query
> select name,age from
> (select name,age,
> (select count(distinct age) from tb b
> where a.age <= b.age) rank
> from tb a) X
> where rank <= 2
>
>
> --
> -Vishal
>
>[/color]

top question

top question

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment