<kalikoi@gmail.com> wrote in message
news:1166425546.236347.145920@73g2000cwn.googlegroups.com...
> Hi
>
> my column values need not be unique...also the sample data i posted is
> the resultant of the query
> as
> select top 10 cola colb,colc where cold=somedate order by cola
> desc,colb desc,colc desc
>
> so can i modify the above query so that i can get my output
> also i shouldn't use Temp tables
>
> Thanks & Regards
> Kalyan
>
> Uri Dimant wrote:
>> I assume that cola ,colb and colc are unique . I mean the data does not
>> repeat in cola , ok?
>>
>> untested
>> select cola,
>> (select count(*) from table t where t.cola<=table.cola) as rankcola,
>> colb,
>> (select count(*) from table t where t.colb<=table.colb) as rankcolb,
>> colc,
>> (select count(*) from table t where t.colc<=table.colc) as rankcolc
>> from table
>> order by rankcola
>>
>>
>>
>> <kalikoi@gmail.com> wrote in message
>> news:1166423786.509159.81410@79g2000cws.googlegroups.com...
>> > Hi
>> >
>> > Am using SQL-2000 and here is my sample data
>> >
>> > cola colb colc
>> > 113700.00 113700.00 85400.00
>> > 1785.00 1800.00 1130.00
>> > 761.00 815.00 690.00
>> > 635.50 846.75 386.55
>> > 587.00 603.00 447.00
>> > 533.00 557.97 354.50
>> > 480.30 513.00 331.55
>> > 480.00 505.00 355.01
>> > 464.75 470.99 308.50
>> > 444.82 449.90 234.59
>> >
>> > and my final output should be
>> >
>> > cola rank colb rank colc rank
>> > ---------------------------------------------------------------------------
>> > 113700.00 1 113700.00 1 85400.00 1
>> > 1785.00 2 1800.00 2 1130.00 2
>> > 761.00 3 815.00 4 690.00 3
>> > 635.50 4 846.75 3 386.55 5
>> > 587.00 5 603.00 5 447.00 4
>> > 533.00 6 557.97 6 354.50 7
>> > 480.30 7 513.00 7 331.55 8
>> > 480.00 8 505.00 8 355.01 6
>> > 464.75 9 470.99 9 308.50 9
>> > 444.82 10 449.90 10 234.59 10
>> >
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> What is the version are you using?
>> >>
>> >> SS2005 --Take a look at ROW_NUMBER() function
>> >> SS2000 --Pleas post DDL+ sample data+ an expected result
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> <kalikoi@gmail.com> wrote in message
>> >> news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
>> >> > Hi
>> >> >
>> >> > i have the following query
>> >> >
>> >> > select top 10 cola,colb,colc from table t where cold=somedate order
>> >> > by
>> >> > cola desc,colb desc,colc desc
>> >> >
>> >> > i have to modify the above query so that each column in the
>> >> > resultant
>> >> > has to be ranked...to be more clear retrieve top 10 values of the
>> >> > three
>> >> > columns from the table and give ranks to each column from the
>> >> > resultant
>> >> > top 10
>> >> >
>> >> > Thanks & Regards
>> >> > Kalyan
>> >> >
>> >
>

<kalikoi@gmail.com> wrote in message
news:1166423786.509159.81410@79g2000cws.googlegroups.com...
> Hi
>
> Am using SQL-2000 and here is my sample data
>
> cola colb colc
> 113700.00 113700.00 85400.00
> 1785.00 1800.00 1130.00
> 761.00 815.00 690.00
> 635.50 846.75 386.55
> 587.00 603.00 447.00
> 533.00 557.97 354.50
> 480.30 513.00 331.55
> 480.00 505.00 355.01
> 464.75 470.99 308.50
> 444.82 449.90 234.59
>
> and my final output should be
>
> cola rank colb rank colc rank
> ---------------------------------------------------------------------------
> 113700.00 1 113700.00 1 85400.00 1
> 1785.00 2 1800.00 2 1130.00 2
> 761.00 3 815.00 4 690.00 3
> 635.50 4 846.75 3 386.55 5
> 587.00 5 603.00 5 447.00 4
> 533.00 6 557.97 6 354.50 7
> 480.30 7 513.00 7 331.55 8
> 480.00 8 505.00 8 355.01 6
> 464.75 9 470.99 9 308.50 9
> 444.82 10 449.90 10 234.59 10
>
>
>
>
> Uri Dimant wrote:
>> What is the version are you using?
>>
>> SS2005 --Take a look at ROW_NUMBER() function
>> SS2000 --Pleas post DDL+ sample data+ an expected result
>>
>>
>>
>>
>>
>> <kalikoi@gmail.com> wrote in message
>> news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
>> > Hi
>> >
>> > i have the following query
>> >
>> > select top 10 cola,colb,colc from table t where cold=somedate order by
>> > cola desc,colb desc,colc desc
>> >
>> > i have to modify the above query so that each column in the resultant
>> > has to be ranked...to be more clear retrieve top 10 values of the three
>> > columns from the table and give ranks to each column from the resultant
>> > top 10
>> >
>> > Thanks & Regards
>> > Kalyan
>> >
>

<kalikoi@gmail.com> wrote in message
news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
> Hi
>
> i have the following query
>
> select top 10 cola,colb,colc from table t where cold=somedate order by
> cola desc,colb desc,colc desc
>
> i have to modify the above query so that each column in the resultant
> has to be ranked...to be more clear retrieve top 10 values of the three
> columns from the table and give ranks to each column from the resultant
> top 10
>
> Thanks & Regards
> Kalyan
>

<kalikoi@gmail.com> wrote in message
news:1166432305.833931.165190@79g2000cws.googlegroups.com...
>
> Hi
>
> Will the identity column gives equal rank for the same values?
>
>
> Uri Dimant wrote:
>> Ok, so add an unique column (could be an IDENTITY) and refer to this
>> column
>> in subquery instead of cola,colb,colc
>>
>>
>>
>>
>>
>>
>>
>> <kalikoi@gmail.com> wrote in message
>> news:1166425546.236347.145920@73g2000cwn.googlegroups.com...
>> > Hi
>> >
>> > my column values need not be unique...also the sample data i posted is
>> > the resultant of the query
>> > as
>> > select top 10 cola colb,colc where cold=somedate order by cola
>> > desc,colb desc,colc desc
>> >
>> > so can i modify the above query so that i can get my output
>> > also i shouldn't use Temp tables
>> >
>> > Thanks & Regards
>> > Kalyan
>> >
>> > Uri Dimant wrote:
>> >> I assume that cola ,colb and colc are unique . I mean the data does
>> >> not
>> >> repeat in cola , ok?
>> >>
>> >> untested
>> >> select cola,
>> >> (select count(*) from table t where t.cola<=table.cola) as rankcola,
>> >> colb,
>> >> (select count(*) from table t where t.colb<=table.colb) as rankcolb,
>> >> colc,
>> >> (select count(*) from table t where t.colc<=table.colc) as rankcolc
>> >> from table
>> >> order by rankcola
>> >>
>> >>
>> >>
>> >> <kalikoi@gmail.com> wrote in message
>> >> news:1166423786.509159.81410@79g2000cws.googlegroups.com...
>> >> > Hi
>> >> >
>> >> > Am using SQL-2000 and here is my sample data
>> >> >
>> >> > cola colb colc
>> >> > 113700.00 113700.00 85400.00
>> >> > 1785.00 1800.00 1130.00
>> >> > 761.00 815.00 690.00
>> >> > 635.50 846.75 386.55
>> >> > 587.00 603.00 447.00
>> >> > 533.00 557.97 354.50
>> >> > 480.30 513.00 331.55
>> >> > 480.00 505.00 355.01
>> >> > 464.75 470.99 308.50
>> >> > 444.82 449.90 234.59
>> >> >
>> >> > and my final output should be
>> >> >
>> >> > cola rank colb rank colc rank
>> >> > ---------------------------------------------------------------------------
>> >> > 113700.00 1 113700.00 1 85400.00 1
>> >> > 1785.00 2 1800.00 2 1130.00 2
>> >> > 761.00 3 815.00 4 690.00 3
>> >> > 635.50 4 846.75 3 386.55 5
>> >> > 587.00 5 603.00 5 447.00 4
>> >> > 533.00 6 557.97 6 354.50 7
>> >> > 480.30 7 513.00 7 331.55 8
>> >> > 480.00 8 505.00 8 355.01 6
>> >> > 464.75 9 470.99 9 308.50 9
>> >> > 444.82 10 449.90 10 234.59 10
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Uri Dimant wrote:
>> >> >> What is the version are you using?
>> >> >>
>> >> >> SS2005 --Take a look at ROW_NUMBER() function
>> >> >> SS2000 --Pleas post DDL+ sample data+ an expected result
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> <kalikoi@gmail.com> wrote in message
>> >> >> news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
>> >> >> > Hi
>> >> >> >
>> >> >> > i have the following query
>> >> >> >
>> >> >> > select top 10 cola,colb,colc from table t where cold=somedate
>> >> >> > order
>> >> >> > by
>> >> >> > cola desc,colb desc,colc desc
>> >> >> >
>> >> >> > i have to modify the above query so that each column in the
>> >> >> > resultant
>> >> >> > has to be ranked...to be more clear retrieve top 10 values of the
>> >> >> > three
>> >> >> > columns from the table and give ranks to each column from the
>> >> >> > resultant
>> >> >> > top 10
>> >> >> >
>> >> >> > Thanks & Regards
>> >> >> > Kalyan
>> >> >> >
>> >> >
>> >
>

<kalikoi@gmail.com> wrote in message
news:1166434886.006389.254690@80g2000cwy.googlegroups.com...
> Hi
> i shouldn't use Temp tables
>
>
>
> Uri Dimant wrote:
>> create table #tmp (id int not null identity(1,1),
>> cola decimal(18,3),
>> colb decimal(18,3),
>> colc decimal(18,3)
>> )
>>
>> insert into #tmp (cola,colb,colc) values (113700.00 ,113700.00, 85400.00)
>> insert into #tmp (cola,colb,colc) values (1785.00 ,815.00 ,690.00)
>> insert into #tmp (cola,colb,colc) values (761.00 ,815.00, 690.00)
>> insert into #tmp (cola,colb,colc) values (587.00 ,603.00 ,447.00)
>>
>>
>> --simple do
>> select cola,id as rankcola,
>> colb,id as rankcolb,
>> colc,id as rankcolc from #tmp
>>
>> --or
>> select cola,
>> (select count(*) from #tmp t where t.id<=#tmp.id) as rankcola,
>> colb,
>> (select count(*) from #tmp t where t.id<=#tmp.id) as rankcolb,
>> colc,
>> (select count(*) from #tmp t where t.id<=#tmp.id) as rankcolc
>> from #tmp
>> order by rankcola
>>
>>
>>
>> <kalikoi@gmail.com> wrote in message
>> news:1166432305.833931.165190@79g2000cws.googlegroups.com...
>> >
>> > Hi
>> >
>> > Will the identity column gives equal rank for the same values?
>> >
>> >
>> > Uri Dimant wrote:
>> >> Ok, so add an unique column (could be an IDENTITY) and refer to this
>> >> column
>> >> in subquery instead of cola,colb,colc
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> <kalikoi@gmail.com> wrote in message
>> >> news:1166425546.236347.145920@73g2000cwn.googlegroups.com...
>> >> > Hi
>> >> >
>> >> > my column values need not be unique...also the sample data i posted
>> >> > is
>> >> > the resultant of the query
>> >> > as
>> >> > select top 10 cola colb,colc where cold=somedate order by cola
>> >> > desc,colb desc,colc desc
>> >> >
>> >> > so can i modify the above query so that i can get my output
>> >> > also i shouldn't use Temp tables
>> >> >
>> >> > Thanks & Regards
>> >> > Kalyan
>> >> >
>> >> > Uri Dimant wrote:
>> >> >> I assume that cola ,colb and colc are unique . I mean the data does
>> >> >> not
>> >> >> repeat in cola , ok?
>> >> >>
>> >> >> untested
>> >> >> select cola,
>> >> >> (select count(*) from table t where t.cola<=table.cola) as
>> >> >> rankcola,
>> >> >> colb,
>> >> >> (select count(*) from table t where t.colb<=table.colb) as
>> >> >> rankcolb,
>> >> >> colc,
>> >> >> (select count(*) from table t where t.colc<=table.colc) as rankcolc
>> >> >> from table
>> >> >> order by rankcola
>> >> >>
>> >> >>
>> >> >>
>> >> >> <kalikoi@gmail.com> wrote in message
>> >> >> news:1166423786.509159.81410@79g2000cws.googlegroups.com...
>> >> >> > Hi
>> >> >> >
>> >> >> > Am using SQL-2000 and here is my sample data
>> >> >> >
>> >> >> > cola colb colc
>> >> >> > 113700.00 113700.00 85400.00
>> >> >> > 1785.00 1800.00 1130.00
>> >> >> > 761.00 815.00 690.00
>> >> >> > 635.50 846.75 386.55
>> >> >> > 587.00 603.00 447.00
>> >> >> > 533.00 557.97 354.50
>> >> >> > 480.30 513.00 331.55
>> >> >> > 480.00 505.00 355.01
>> >> >> > 464.75 470.99 308.50
>> >> >> > 444.82 449.90 234.59
>> >> >> >
>> >> >> > and my final output should be
>> >> >> >
>> >> >> > cola rank colb rank colc rank
>> >> >> > ---------------------------------------------------------------------------
>> >> >> > 113700.00 1 113700.00 1 85400.00 1
>> >> >> > 1785.00 2 1800.00 2 1130.00 2
>> >> >> > 761.00 3 815.00 4 690.00 3
>> >> >> > 635.50 4 846.75 3 386.55 5
>> >> >> > 587.00 5 603.00 5 447.00 4
>> >> >> > 533.00 6 557.97 6 354.50 7
>> >> >> > 480.30 7 513.00 7 331.55 8
>> >> >> > 480.00 8 505.00 8 355.01 6
>> >> >> > 464.75 9 470.99 9 308.50 9
>> >> >> > 444.82 10 449.90 10 234.59 10
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > Uri Dimant wrote:
>> >> >> >> What is the version are you using?
>> >> >> >>
>> >> >> >> SS2005 --Take a look at ROW_NUMBER() function
>> >> >> >> SS2000 --Pleas post DDL+ sample data+ an expected result
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> <kalikoi@gmail.com> wrote in message
>> >> >> >> news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
>> >> >> >> > Hi
>> >> >> >> >
>> >> >> >> > i have the following query
>> >> >> >> >
>> >> >> >> > select top 10 cola,colb,colc from table t where cold=somedate
>> >> >> >> > order
>> >> >> >> > by
>> >> >> >> > cola desc,colb desc,colc desc
>> >> >> >> >
>> >> >> >> > i have to modify the above query so that each column in the
>> >> >> >> > resultant
>> >> >> >> > has to be ranked...to be more clear retrieve top 10 values of
>> >> >> >> > the
>> >> >> >> > three
>> >> >> >> > columns from the table and give ranks to each column from the
>> >> >> >> > resultant
>> >> >> >> > top 10
>> >> >> >> >
>> >> >> >> > Thanks & Regards
>> >> >> >> > Kalyan
>> >> >> >> >
>> >> >> >
>> >> >
>> >
>

Uri Dimant wrote:
> Ok, so add an unique column (could be an IDENTITY) and refer to this column
> in subquery instead of cola,colb,colc
>
>
>
>
>
>
>
> <kalikoi@gmail.com> wrote in message
> news:1166425546.236347.145920@73g2000cwn.googlegroups.com...
> > Hi
> >
> > my column values need not be unique...also the sample data i posted is
> > the resultant of the query
> > as
> > select top 10 cola colb,colc where cold=somedate order by cola
> > desc,colb desc,colc desc
> >
> > so can i modify the above query so that i can get my output
> > also i shouldn't use Temp tables
> >
> > Thanks & Regards
> > Kalyan
> >
> > Uri Dimant wrote:
> >> I assume that cola ,colb and colc are unique . I mean the data does not
> >> repeat in cola , ok?
> >>
> >> untested
> >> select cola,
> >> (select count(*) from table t where t.cola<=table.cola) as rankcola,
> >> colb,
> >> (select count(*) from table t where t.colb<=table.colb) as rankcolb,
> >> colc,
> >> (select count(*) from table t where t.colc<=table.colc) as rankcolc
> >> from table
> >> order by rankcola
> >>
> >>
> >>
> >> <kalikoi@gmail.com> wrote in message
> >> news:1166423786.509159.81410@79g2000cws.googlegroups.com...
> >> > Hi
> >> >
> >> > Am using SQL-2000 and here is my sample data
> >> >
> >> > cola colb colc
> >> > 113700.00 113700.00 85400.00
> >> > 1785.00 1800.00 1130.00
> >> > 761.00 815.00 690.00
> >> > 635.50 846.75 386.55
> >> > 587.00 603.00 447.00
> >> > 533.00 557.97 354.50
> >> > 480.30 513.00 331.55
> >> > 480.00 505.00 355.01
> >> > 464.75 470.99 308.50
> >> > 444.82 449.90 234.59
> >> >
> >> > and my final output should be
> >> >
> >> > cola rank colb rank colc rank
> >> > ---------------------------------------------------------------------------
> >> > 113700.00 1 113700.00 1 85400.00 1
> >> > 1785.00 2 1800.00 2 1130.00 2
> >> > 761.00 3 815.00 4 690.00 3
> >> > 635.50 4 846.75 3 386.55 5
> >> > 587.00 5 603.00 5 447.00 4
> >> > 533.00 6 557.97 6 354.50 7
> >> > 480.30 7 513.00 7 331.55 8
> >> > 480.00 8 505.00 8 355.01 6
> >> > 464.75 9 470.99 9 308.50 9
> >> > 444.82 10 449.90 10 234.59 10
> >> >
> >> >
> >> >
> >> >
> >> > Uri Dimant wrote:
> >> >> What is the version are you using?
> >> >>
> >> >> SS2005 --Take a look at ROW_NUMBER() function
> >> >> SS2000 --Pleas post DDL+ sample data+ an expected result
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> <kalikoi@gmail.com> wrote in message
> >> >> news:1166420589.817512.74670@t46g2000cwa.googlegroups.com...
> >> >> > Hi
> >> >> >
> >> >> > i have the following query
> >> >> >
> >> >> > select top 10 cola,colb,colc from table t where cold=somedate order
> >> >> > by
> >> >> > cola desc,colb desc,colc desc
> >> >> >
> >> >> > i have to modify the above query so that each column in the
> >> >> > resultant
> >> >> > has to be ranked...to be more clear retrieve top 10 values of the
> >> >> > three
> >> >> > columns from the table and give ranks to each column from the
> >> >> > resultant
> >> >> > top 10
> >> >> >
> >> >> > Thanks & Regards
> >> >> > Kalyan
> >> >> >
> >> >
> >