| Max value [message #373893] |
Wed, 16 May 2001 10:14  |
M. Cassidy
Messages: 3
Registered: May 2001
|
Junior Member |
|
|
Hi,
I know this is suppose to be simple query but I was not able to get the expected result as it should be.
I wanted to find the biggest house in each zipcode, print zipcode, houseid, and totalarea
in the table data shows
zipcode houseid totalarea
20877 22222 1424.99
30009 12001 3475.0
30009 12345 1501.96
30009 33445 2148.0599999999999
30009 92500 1680.45
run this query
select distinct zipcode, max(totalarea)
from house
group by zipcode
that's how I got it but how can I put the houseid
zipcode totalarea
20877 1424.99
30009 3475.0
I want to get the result that should be
zipcode houseid totalarea
20877 22222 1424.99
30009 12001 3475.0
thanks
|
|
|
|
| Re: Max value [message #373896 is a reply to message #373893] |
Wed, 16 May 2001 12:47  |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
select b.zipcode, b.totalarea, a.houseid
from house a, (select zipcode, max(totalarea)totalarea
from house
group by zipcode) b
where a.zipcode = b.zipcode
and a.totalarea = b.totalarea
that's how I got it but how can I put the houseid
|
|
|
|
| Re: Max value [message #373898 is a reply to message #373896] |
Wed, 16 May 2001 13:04 |
M. Cassidy
Messages: 3
Registered: May 2001
|
Junior Member |
|
|
I am still not getting this right... I tried this sql query that you gave me and it's not working. How do you get this result?
Can you please show the sql query along with the result output?
I was expecting to get the output result should be
zipcode houseid totalarea
20877 22222 1424.99
30009 12001 3475.0
Find the biggest house with the largest total area in each zipcode
I wanted to get the expected answer above:
|
|
|
|
| Re: Max value [message #373900 is a reply to message #373896] |
Wed, 16 May 2001 13:09 |
M. Cassidy
Messages: 3
Registered: May 2001
|
Junior Member |
|
|
I am still not getting this right... I tried this sql query that you gave me and it's not working. How do you get this result?
Can you please show the sql query along with the result output?
I was expecting to get the output result should be
zipcode houseid totalarea
20877 22222 1424.99
30009 12001 3475.0
Find the biggest house with the largest total area in each zipcode
I wanted to get the expected answer above:
|
|
|
|
| Re: Max value [message #373906 is a reply to message #373893] |
Thu, 17 May 2001 01:48 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi,
Can you please check whether this query can be useful.
SQL> select zipcode,houseid,totalarea from zip a where totalarea =
2 (select max(totalarea) from zip b where a.zipcode = b.zipcode);
ZIPCODE HOUSEID TOTALAREA
---------- ---------- ---------
20877 22222 1424.99
30009 12001 3475
HTH.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: Max value [message #373925 is a reply to message #373896] |
Thu, 17 May 2001 05:54 |
Harika
Messages: 6
Registered: May 2001
|
Junior Member |
|
|
hi there
try out this
select * from house a
where totalarea in (select max(totalarea)
from house b
group by zipcode
having a.houseid=b.houseid)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
