# TOP 10 Distinct rows

• Hi guys,

So here is my problem

I have a view which has two columns named CNO and SNO

it goes like

CNO | SNO

63 1

63 1

63 2

63 2

63 3

... ...

46 11

... ...

56 20

63 21

Now basically what i want to do is that i need to get the top 10 distinct CNO numbers from this

view.

I am a novice to T-sql so i am not sure how to do this.

Could anyone help me out on the same.

Thanks

Jacob

• select distinct top 10 CNO

from MyTable

• thanks for the help but the problem here is that the order is changing and thats something which i don;t want. Its like when do the query the values returned are in sorted order

• what i am saying is my return value should be

63

46

56

... and so on

Thanks for ur help

• Check this out...

`SELECTTOP 10 T.*`

`FROM( SELECT CNO, MAX( SNO ) AS SNO FROM SomeTable GROUP BY CNO ) T`

`ORDER BY SNO DESC`

--Ramesh

• Hey Ramesh,

Thanks a lot man. This exactly what i wanted.

• I'm glad, I could help.

--Ramesh

• Select * from SomeTable

where SNO>=

(

Select MIN(RR.R) from

(

Select TOP 10 T.SNOS as R

FROM

(SELECT CNO, Min( SNO ) AS SNOS FROM SomeTable GROUP BY CNO ) T

ORDER BY T.SNOS ASC

) RR)

and

SNO<=

(Select MAX(RR.R) from

(

Select TOP 10 T.SNOS as R

FROM

(SELECT CNO, Min( SNO ) AS SNOS FROM SomeTable GROUP BY CNO ) T

ORDER BY T.SNOS ASC

) RR)

Okie now this query will basically return me the results associated with the top 10 distinct CNOs. ranging from the MIN and Max values of SNO.

Now my question is how i will get the next 10 results. ???

• It would be hard for us to provide the best solution without knowing what you exactly want from us. Its there in your mind but not mine.

Looking at the query, I can say this can be optimized to a better level.

--Ramesh

• hi jacobneroth ,

will u mention exactly wht u need?

thx

sreejith

• As a few others have said, you aren't being exactly clear as to what it is that you are actually trying to do.

Perhaps if you explain a little about what you are trying to do, we might be able to help more.

You seem to be leading us down a path, but it is your path. It might be that a different path is much more efficient.

It sounds like you are trying to page though some data. Top 10, then 11-20, 21-30 etc. right?

Ok that's fine, but WHY? This sounds like application level issues, not DB issues. Is there a reason why you think you can only process 10 rows at a time?

• Again - without some specifics it's hard to advise you on anything. That being said - you might care to check out the NTILE ranking function, which should help you pull stuff in predictable chunks.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Hi, my apologies if I put this answer in the wrong place (its my first time replying to a query here). one way to get the second ten results would be to run the query twice as two sub queries, select the top ten in one sub query and the top twenty in the second sub query, joining those two sub queries together to filter out the results common to both, leaving you with (in theory...) the second ten records. its not the most elegant solution, but should work

Cheers

Viewing 13 posts - 1 through 12 (of 12 total)