Post reply

Get First row value

  • May 7, 2008 at 4:48 am

    #811663

    to get the employee data with the number

    try this query

    this query gives employee data with the number

    select MID, Status, Code, row_number() over (partition by code order by MID) as number

    from Emp

    to get the top 1 employees

    select MID, Status, Code

    FROM

    (

    select MID, Status, Code, row_number() over (partition by code order by MID) as number

    from Emp) AS TAB

    where number = 1

  • May 7, 2008 at 9:11 am

    #811872

    Gila,

    How many different codes are there?

    Around 2000 codes are there.

    karthik

  • May 7, 2008 at 9:14 am

    #811875

    Just i am thinking ,why can't we create the running number based on the code ?

    Running number has to increase when there is no change found on the CODE,if found ,it should start with 1 again like that shall we create ?

    karthik

  • May 7, 2008 at 9:25 am

    #811887

    I tried the below code.

    create table #emp

    (

    mid int,

    status char(1),

    code varchar(5)

    )

    insert into #emp

    select 1,'A','1X'

    union all

    select 25,'A','1X'

    union all

    select 37, 'T','1X'

    union all

    select 42,'T','2X'

    union all

    select 58,'A','2X'

    union all

    select 61,'A','3X'

    select * from #emp

    select code,N,count(code)

    from #emp,Tally

    group by code,N

    having N <= count(code)

    I think i am very near to the solution.But who know it ?

    But it is very slow,i don't know why TALLY table also executing slowly.

    Execution Time 32.

    SQL Server cpu time: 3200 ms. SQL Server elapsed time: 3140 ms.

    I remember that Tally table shouldn't take more time.

    Suggestions are welcome !

    karthik

  • May 7, 2008 at 9:29 am

    #811888

    i got the below output

    code N Count

    ---- ------- -----------

    1X 1 3

    1X 2 3

    1X 3 3

    2X 1 2

    2X 2 2

    3X 1 1

    karthik

  • May 8, 2008 at 8:14 am

    #812432

    Any Comments or suggestions?

    karthik

  • May 12, 2008 at 1:52 am

    #813639

    Yes, does my solution (bottom pg 3) work or not?

  • May 12, 2008 at 5:09 am

    #813693

    The Tally table cannot be used to solve ranking problems because of the inherent cross-join. You've limited the cross-join, but only with a triangular join... half as bad but still very bad.

    There's no such thing as "Physical Order" in a database. You can have an implied order based on an index, but unless it's a Clustered index, you can end up with the "merry-go-round" order that I spoke of in the Running Total article.

    What is the Clustered index of the table, Karthik? If there isn't one and the MID column can't be used to establish the "natural" order (as you've already stated), this problem cannot be solved. Sorry...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 12, 2008 at 7:55 am

    #813804

    Jeff,

    Thanks for highlighting some good points.

    Table doesn't have a single clustered index. But it have non clustered index on mid.

    Will it help to resolve this running total issue ?

    Janine,

    Thanks for your reply.

    If you can make the assumption "Smallest MID is always the 'first' row for each code" then this should work:

    I can't assume smallest MID is always the 'first' row for each code.

    karthik

  • May 12, 2008 at 8:05 am

    #813814

    Table doesn't have a single clustered index. But it have non clustered index on mid.

    Will it help to resolve this running total issue ?

    Only if the smallest MID was the value you want. Whcih, as you've said, it's not.

    You're probably going to have to create a column to specify which status is first for each code and update it manually.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2008 at 8:05 am

    #813815

    karthikeyan (5/12/2008)

    Jeff,

    Thanks for highlighting some good points.

    Table doesn't have a single clustered index. But it have non clustered index on mid.

    Will it help to resolve this running total issue ?

    Janine,

    Thanks for your reply.

    If you can make the assumption "Smallest MID is always the 'first' row for each code" then this should work:

    I can't assume smallest MID is always the 'first' row for each code.

    Non-clustered indexes don't help with this particular problem. The running update process doesn't ackowledge that ordering during the updates, so you end up with junk results.

    Like Jeff mentioned - it's clustered or no go. (unless you start adding something else to tell which is "first")

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

  • May 19, 2008 at 5:22 am

    #817090

    So as your people said , table should be created with clustered index.

    But unfortunately table doesn't have clustered index.

    what is the solution to solve this problem ?

    i.e do i have create clustered index ? if yes, won't it affect the other proc's ?

    what can i do further to resolve it ?

    karthik

  • May 19, 2008 at 6:03 am

    #817109

    As I said earlier, you have to define what "first" means first... what do you consider to be the correct order of the data? Can't help unless we know that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 31 through 42 (of 42 total)

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