Loading ...
Sorry, an error occurred while loading the content.

I need urgent help in my project

Expand Messages
  • pnagrecha
    Hi, Lets take an exmple of table emp I want to select changed columns of the records of the table and order it by ascending order of empno and compare deptno
    Message 1 of 2 , Sep 3, 2003
    • 0 Attachment
      Hi,
      Lets take an exmple of table emp
      I want to select changed columns of the records of the table and
      order it by ascending order of empno and compare deptno of first
      record with the deptno of the second record..second record with the
      third record, third record with the fourth record and so on... if the
      deptno is different from the previous record the it should be the
      part of the output else it should not be

      for example
      EMPNO name job manager deptno

      7369 SMITH CLERK 7902 800 20
      7499 ALLEN SALESMAN 7698 300 60 10
      7521 WARD SALESMAN 7698 500 30 10
      7566 JONES MANAGER 7839 2975
      7654 MARTIN SALESMAN 7698 1400 30 20
      7698 BLAKE MANAGER 7839 2850 30
      7782 CLARK MANAGER 7839 2450 10
      7788 SCOTT ANALYST 7566 3000 20
      7844 TURNER SALESMAN 7698 0 30
      7876 ADAMS CLERK 7788 1100 20
      7900 JAMES CLERK 7698 950 30
      7902 FORD ANALYST 7566 3000 20
      7934 MILLER CLERK 7782 1300 10

      in this example the deptno of first record(20) is different then
      second deptno(10),(so output should have 20 that is the first record)
      then compare the second deptno (10) with the third which is same that
      is 10 each so skip... then compare 10 with the NULL value next so
      show 10 and so on.........

      is there anyway to do this by having a query or should we use
      coursors in teh stored procedure
      i need urgent help
      thanks in advance
    • Damhuis Anton
      Because you need to go through the result set in a specified order, you will need to use a CURSOR. So yes in this example it would be best in a SP. So
      Message 2 of 2 , Sep 3, 2003
      • 0 Attachment
        Because you need to go through the result set in a specified order, you will
        need to use a CURSOR. So yes in this example it would be best in a SP.

        So basically do the following:
        Select * into #TempTable -- Your Current Records as per email
        -- Create a Cursor reading from #TempTable
        -- Put the deptno into variable1
        -- Create a loop
        -- Read in next variable (variable2)
        -- Check variable1 to variable2
        -- If same
        -- Do Nothing
        -- else
        -- Insert current Row into #ResultTable
        -- end Loop
        -- close Cursor

        If you wanted it into a single select you would need to do something like:
        (pure speculation now)
        select * from table
        where deptno <> deptno2
        and EMPNO1 < EMPNO2
        and EMPNO2 = (Select Min(EmpNo2) from table where EmpNo2 >
        EMPNO1 )
        (this is written more in English then SQL)

        The Idea is to get the Statement to check the current ROW against
        the next highest entry. I am not sure if this will work or not. Never
        done something like this.


        Regards
        Anton


        -----Original Message-----
        From: pnagrecha [mailto:pnagrecha@...]
        Sent: 03 September 2003 09:17
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] I need urgent help in my project


        Hi,
        Lets take an exmple of table emp
        I want to select changed columns of the records of the table and
        order it by ascending order of empno and compare deptno of first
        record with the deptno of the second record..second record with the
        third record, third record with the fourth record and so on... if the
        deptno is different from the previous record the it should be the
        part of the output else it should not be

        for example
        EMPNO name job manager deptno

        7369 SMITH CLERK 7902 800 20
        7499 ALLEN SALESMAN 7698 300 60 10
        7521 WARD SALESMAN 7698 500 30 10
        7566 JONES MANAGER 7839 2975
        7654 MARTIN SALESMAN 7698 1400 30 20
        7698 BLAKE MANAGER 7839 2850 30
        7782 CLARK MANAGER 7839 2450 10
        7788 SCOTT ANALYST 7566 3000 20
        7844 TURNER SALESMAN 7698 0 30
        7876 ADAMS CLERK 7788 1100 20
        7900 JAMES CLERK 7698 950 30
        7902 FORD ANALYST 7566 3000 20
        7934 MILLER CLERK 7782 1300 10

        in this example the deptno of first record(20) is different then
        second deptno(10),(so output should have 20 that is the first record)
        then compare the second deptno (10) with the third which is same that
        is 10 each so skip... then compare 10 with the NULL value next so
        show 10 and so on.........

        is there anyway to do this by having a query or should we use
        coursors in teh stored procedure
        i need urgent help
        thanks in advance






        Yahoo! Groups Sponsor
        ADVERTISEMENT




        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


        Confidentiality Warning
        =======================
        The contents of this e-mail and any accompanying documentation
        are confidential and any use thereof, in what ever form, by anyone
        other than the addressee is strictly prohibited.
      Your message has been successfully submitted and would be delivered to recipients shortly.