Post reply

TOP 10 Distinct rows

  • December 5, 2007 at 8:01 am

    #181063

    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

  • December 5, 2007 at 10:46 am

    #757399

    select distinct top 10 CNO

    from MyTable

  • December 5, 2007 at 9:38 pm

    #757504

    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

  • December 5, 2007 at 10:06 pm

    #757508

    what i am saying is my return value should be

    63

    46

    56

    ... and so on

    Thanks for ur help

  • December 6, 2007 at 12:26 am

    #757550

    Check this out...

    SELECTTOP 10 T.*

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

    ORDER BY SNO DESC

    --Ramesh

  • December 6, 2007 at 2:01 am

    #757570

    Hey Ramesh,

    Thanks a lot man. This exactly what i wanted.

  • December 6, 2007 at 2:49 am

    #757585

    I'm glad, I could help.

    --Ramesh

  • December 6, 2007 at 4:24 am

    #757601

    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. ???

  • December 6, 2007 at 5:13 am

    #757609

    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

  • January 24, 2008 at 3:28 pm

    #771527

    hi jacobneroth ,

    will u mention exactly wht u need?

    thx

    sreejith

    MCAD

  • January 25, 2008 at 2:13 pm

    #772026

    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?

  • January 25, 2008 at 2:25 pm

    #772029

    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?

  • February 20, 2008 at 12:59 am

    #780100

    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)

You must be logged in to reply to this topic. Login to reply