Results 1 to 6 of 6

Thread: Sql Plus Query

  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Red face Unanswered: Sql Plus Query

    I am having trouble with the following SQL query. I need to get the last 50 unique a.lot ids. although my query returns multiple rows for the same a.lot ids since there can be several comments stored in c.cmt_text. I need to count/ return the last 50 distinct a.lot ids, no matter how many rows that may be. I assume there is no way to concatenate the comments (by row) based on duplicate a.lot/ b.val_1's.

    Example output:
    a.lot b.val_1 c.cmt_text
    --------- ------------ --------------------------------
    12 124.2 example text
    12 124.2 with multiple cmt_text
    13 139.5 comment


    HERE IS WHAT I AM USING NOW (which does not work due to multiple rows due to multiple comments in c.cmt_text):

    select * FROM (
    select a.lot, b.val_1, c.cmt_text
    from a, b, c
    where a.point_id = b.point_id
    and c.point_id = a.point_id
    and a.facility='RAD1'
    and c.facility = a.facility
    and b.facility = a.facility
    and a.chart = 'R3DXMTLLD'
    and c.chart = a.chart
    and b.chart = a.chart
    ORDER BY a.point_dttm DESC)
    where rownum < 50;


    Thanks for any HELP !!
    Rgrds,
    KB

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Sql Plus Query

    Maybe you can use analytics?:

    select * from
    (
    select a.lot, b.val_1, c.cmt_text,
    dense_rank() over (order by a.lot DESC) rnk
    from ...
    ) where rnk <= 50;

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Re: Sql Plus Query

    Thank you sir. This works great!!
    Regards, KB


    Originally posted by andrewst
    Maybe you can use analytics?:

    select * from
    (
    select a.lot, b.val_1, c.cmt_text,
    dense_rank() over (order by a.lot DESC) rnk
    from ...
    ) where rnk <= 50;

  4. #4
    Join Date
    Sep 2011
    Posts
    2

    new post

    hey i am very new to this site as well as to oracle also.
    i didnt find an option where to my post my querry.

    i am very new to oracle.

    please anyone suggest me how can i get both ename and mgr name from both single table based on mgr id in a single query.This is on a basic emp table provided byoracle

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select e.ename, b.ename from emp e, emp b where e.mgr = b.empno;
    
    ENAME	   ENAME
    ---------- ----------
    FORD	   JONES
    SCOTT	   JONES
    JABBAR	   BLAKE
    RAFIQ	   BLAKE
    KARIN	   BLAKE
    SALAM	   BLAKE
    JAMES	   BLAKE
    TURNER	   BLAKE
    MARTIN	   BLAKE
    WARD	   BLAKE
    ALLEN	   BLAKE
    
    ENAME	   ENAME
    ---------- ----------
    MILLER	   CLARK
    ADAMS	   SCOTT
    RAHIM	   KING
    CLARK	   KING
    BLAKE	   KING
    JONES	   KING
    
    17 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2011
    Posts
    2
    thank you

Posting Permissions

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