Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: SQL Query !

  1. #1
    Join Date
    Dec 2011
    Posts
    7

    Post Unanswered: SQL Query !

    Hi there.. I am new to this SQL query writing.

    I have a table ROADS in which i need to filter out some records. The table SS is attached with this post.

    As you can see, there are several entries having same S_GISID but different H_surveyed_length. I need to filter out unique S_GISID records which has the higher H_surveyed_length.

    Please guide. And suggest me some good tutorials to study these.

    Thanks !
    Attached Thumbnails Attached Thumbnails Table.png  

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >As you can see,
    no, not really.

    too bad COPY & PASTE are broken for you.
    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.

  3. #3
    Join Date
    Dec 2011
    Posts
    7
    Cant see?.. The attachment is right there.. My bad !

    Anyways, here ImageShack® - Online Photo and Video Hosting

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please post your table definition as CREATE TABLE statements.

    And ideally some sample data (as INSERT INTO) and the expected output that you want to see.

  5. #5
    Join Date
    Dec 2011
    Posts
    7
    Alright !

    create table roads (ID nvarchar2(255), Length number(38,8));
    insert into roads values (22,34.45);
    insert into roads values (22,56.75);
    insert into roads values (22,23.32);
    insert into roads values (22,12.98);

    ID Length
    22 34.45
    22 56.75
    31 23.32
    31 12.98

    The expected output from this table must have one record for each unique ID with has bigger value in length field.

    ID Length
    22 56.75
    31 23.32

    ????
    Last edited by Ju12Ju; 12-14-11 at 15:39.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT r1.id, r1.length
    FROM roads r1
    WHERE r1.length = (SELECT max(r2.length) 
                       FROM roads r2
                       WHERE r2.id = r1.id)

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It is bad idea to have column name the same as Oracle defined function "LENGTH".
    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.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another option:
    Code:
    select r.id, max(r.length) max_len
    from roads r
    group by r.id;

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    Another option:
    Code:
    select r.id, max(r.length) max_len
    from roads r
    group by r.id;
    And much better than my solution

  10. #10
    Join Date
    Dec 2011
    Posts
    7
    Thanks a lot guys I am slowly learning these things !.. Can you please tell me whats wrong with the following simple query?

    update ww.pr14postcodes a set a.cwregioncode='EN' where b.regioncode='EN' from BND.cwbWorkRegion b and SDO_INSIDE(a.shape,b.shape)='TRUE';

    I just do not know where to give the "from BND.cwbWorkRegion b".. It keeps throwing me errors as "SQL command not properly ended"..

    pls !?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    UPDATE statement never contains "FROM"
    only SELECT contains FROM clause
    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.

  12. #12
    Join Date
    Dec 2011
    Posts
    7
    Yeah.. Okay.. how shall i workout this scenario then?

    I need to give the second table name & its alias name somwhere..

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE ww.pr14postcodes a
    SET    a.cwregioncode = 'EN'
    WHERE  b.regioncode = 'EN'
           AND bnd.cwbworkregion != b
           AND Sdo_inside(a.shape, b.shape) = 'TRUE';
    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.

  14. #14
    Join Date
    Dec 2011
    Posts
    7
    Nope.. It throws the following..

    Code:
    ORA-00904: "B"."SHAPE": invalid identifier
    But the following select command works well and gives output !
    Code:
    select a.postcode from ww.pr14postcodes a, BND.cwbWorkRegion b 
    where b.regioncode='EN' 
    and 
    SDO_INSIDE(A.shape,B.shape) = 'TRUE'

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE ww.pr14postcodes a
    SET    a.cwregioncode = 'EN'
    WHERE  EXISTS (SELECT a.postcode
                   FROM   ww.pr14postcodes a,
                          bnd.cwbworkregion b
                   WHERE  b.regioncode = 'EN'
                          AND Sdo_inside(a.shape, b.shape) = 'TRUE')
    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.

Posting Permissions

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