| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
12-14-11, 13:53
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 7
|
|
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 !
|
|
12-14-11, 13:59
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
>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.
|
|
12-14-11, 14:08
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 7
|
|
|
|
12-14-11, 14:15
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
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.
|
|
12-14-11, 14:30
|
|
Registered User
|
|
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 14:39.
|
12-14-11, 14:40
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Code:
SELECT r1.id, r1.length
FROM roads r1
WHERE r1.length = (SELECT max(r2.length)
FROM roads r2
WHERE r2.id = r1.id)
|
|
12-14-11, 14:46
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
12-14-11, 15:55
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
Another option:
Code:
select r.id, max(r.length) max_len
from roads r
group by r.id;
|
|
12-15-11, 02:38
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by Littlefoot
Another option:
Code:
select r.id, max(r.length) max_len
from roads r
group by r.id;
|
And much better than my solution 
|
|
12-22-11, 13:40
|
|
Registered User
|
|
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 !?
|
|
12-22-11, 13:42
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
12-22-11, 13:43
|
|
Registered User
|
|
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..
|
|
12-22-11, 13:46
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
12-22-11, 13:50
|
|
Registered User
|
|
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'
|
|
12-22-11, 21:30
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL Query !
