Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Exclamation Unanswered: oracle

    Hey Everyone,

    Can you guys plz help me with this problem ive been having (im a newb),

    example: How would I be able to select the first and last record of the pcc group categories?

    PCC-- ID ----NAME--- PTM
    ------ ----------- -------------------------------
    SEC 184744 SMITH RTENM
    SEC 184744 SMITH IOIUNE
    SEC 184744 SMITH TESDD
    PRO 989776 TOMM DEERT
    PRO 989776 TOMM FFFFGS
    PRO 989776 TOMM DHHHS
    BEG 909833 STEVE HEEDA
    BEG 909833 STEVE SERBA
    BEG 909833 STEVE ASGRR


    example output for the first of each group would be:

    PCC----ID---NAME--- PTM
    ------ ----------- -------------------------
    SEC 184744 SMITH RTENM
    PRO 989776 TOMM DEERT
    BEG 909833 STEVE HEEDA

    cheers, keep up the good work
    Last edited by maestro; 11-01-03 at 00:15.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm not quite sure how you decide which is the first row in a group of PCC, ID, NAME. Ie there doesn't seem to be any alphabetical ordering of the PTM column.

    If the first/last were defined by alpha sorting, then this would do the job for first in each group,

    PHP Code:
    select pcc,id,name,min(ptm)
    from <yourtablename>
    group by pcc,id,name 
    and for the last, change min( ptm ) to max( ptm ). This may not be exactly what you require but if you can't figure out how to determine first/last let us know the ordering and someone will be sure to help.

    Hth
    Bill

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by billm
    Hi,

    I'm not quite sure how you decide which is the first row in a group of PCC, ID, NAME. Ie there doesn't seem to be any alphabetical ordering of the PTM column.

    If the first/last were defined by alpha sorting, then this would do the job for first in each group,

    PHP Code:
    select pcc,id,name,min(ptm)
    from <yourtablename>
    group by pcc,id,name 
    Bill
    Try something like:

    select pcc,id,name,min(ptm), "Minimum" as value_type
    from <yourtablename>
    group by pcc,id,name
    UNION
    select pcc,id,name,max(ptm), "Maximum" as value_type
    from <yourtablename>
    group by pcc DESC,id,name

  4. #4
    Join Date
    Jul 2003
    Posts
    129
    btw, when are we use the UNION ?

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    U use to create one resultset from two different queries with the same columns. UNION combines the resultsets from two queries, filters out the double records and shows it as one resultset. Note that the checking for double records might be expensive. There's also UNION ALL, which does not check for double records. And there is MINUS, which creates a resulltset from records found bu query1 MINUS the rows returned by query2.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Oct 2003
    Posts
    3
    thanks for your help guys, really appreciate it

  7. #7
    Join Date
    Jul 2003
    Posts
    129
    i tried the following sql statement
    Code:
    (select name from students)union(select surname from students)
    but i received only the surname of the students in the result set

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Originally posted by dominant
    i tried the following sql statement
    Code:
    (select name from students)union(select surname from students)
    but i received only the surname of the students in the result set
    Two issues here:

    first, always use the same set of columns. If u are selecting different columns from different tables, be sure to add a dummy in the other query. Like this.

    select empno, to_char(null) deptno from emp
    union
    select to_char(null) empno, deptno from dept

    Second, u are querying the same table. Then Oracle will filter all records for doubles, that might be the reasion u only see surname (but i would have expected u'd see the name). Try a statement like above i u want a working example.

    Hope that helps.
    Edwin van Hattem
    OCP DBA / System analyst

  9. #9
    Join Date
    Jul 2003
    Posts
    129
    i tried some quieries with no success.

    How can i execute two queries on the same table and different columns?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •