Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: sub query grrrrr. help!

    Ok I have a database for an estate agents, with a atbke called saleProperty

    I want to select the highest and lowest priced property of each property type - there is a filed called type in the saleProperty table which holds data like 'Terrace', 'Detached' etc...

    So I got very confused by this and assumed I would have to explicitly declare each possible with a sub query i.e.

    SELECT *
    FROM saleProperty
    WHERE (price IN (SELECT MAX(price) FROM saleProperty WHERE type='detached') AND type='detacted')
    OR (price IN (SELECT MAX(price) FROM saleProperty WHERE type='semi-detached') AND type='semi-detacted')....etc

    this seemsed a little OTT though and not very dynamic as every proprty type would have to be declared seperatley. I spoke to a lecturer who said oooo, thats easy just use IN. So i did

    SELECT *
    FROM SaleProperty
    WHERE (price IN (SELECT MAX(price)
    FROM SaleProperty
    WHERE dateSold Is Null
    GROUP BY type))
    OR (price IN (SELECT MIN(price)
    FROM SaleProperty
    WHERE dateSold Is Null
    GROUP BY type))
    ORDER BY type, price;

    Isn't that lovely, just one massive frikin problem! if there is a terrace property with a value of 90000 and the max priced terrace is 91000 and the min priced terrace is 89000, if the max priced detached house happens to be 90000, then the 90000 terrace property will be selected as well! So surely the IN statment can;t be used here, becuase it's only comparing the prices (this is why I thuoght it was in-appropriate to start with) not the prices and the type! Is there a way to overcome this?

    Thanks in advanced (damn lecturers hehe)

  2. #2
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    Hi, try this
    Code:
    select type,max(Price),min(Price) from saleProperty 
    where Date is null group by type
    Last edited by Madhivanan; 12-10-04 at 09:25.
    Madhivanan

    Failing to plan is Planning to fail

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    I need to output the full details of each max, min property of each type, notjust the max and min prices for different types of properties.

  4. #4
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    Try this
    Code:
    select type,col1,col2,etc,
    (select max(Price) from saleProperty where g1.type=g2.type group by type),
    (select min(Price) from saleProperty g1 where g1.type=g2.type group by 
    type ) from saleProperty g2 group by type,col1,col2,etc
    Madhivanan

    Failing to plan is Planning to fail

  5. #5
    Join Date
    Jun 2002
    Posts
    63
    I just get a massivly long list of all the properties, including their repective min and max price for each type:

    Code:
    SELECT g2.type, propertyID,
    (select max(Price) from saleProperty g1 where g1.type=g2.type group by type) AS maxprice, 
    (select min(Price) from saleProperty g1 where g1.type=g2.type group by type ) AS minprice
    FROM saleProperty AS g2
    GROUP BY type, propertyID;
    Produces

    Code:
    type	propertyID	maxprice	minprice
    Detached	3	598,000.00	95,000.00
    Detached	324	598,000.00	95,000.00
    Detached	354	598,000.00	95,000.00
    Detached	422	598,000.00	95,000.00
    Detached	766	598,000.00	95,000.00
    Detached	786	598,000.00	95,000.00
    Detached	821	598,000.00	95,000.00
    Semi-detached	254	258,000.00	89,500.00
    Semi-detached	287	258,000.00	89,500.00
    Semi-detached	321	258,000.00	89,500.00
    Semi-detached	388	258,000.00	89,500.00
    Semi-detached	467	258,000.00	89,500.00
    Semi-detached	721	258,000.00	89,500.00
    Semi-detached	734	258,000.00	89,500.00
    Semi-detached	854	258,000.00	89,500.00
    Terrace	1	260,000.00	82,000.00
    Terrace	56	260,000.00	82,000.00
    Terrace	61	260,000.00	82,000.00
    Terrace	62	260,000.00	82,000.00
    Terrace	234	260,000.00	82,000.00
    Terrace	265	260,000.00	82,000.00
    Terrace	367	260,000.00	82,000.00
    Terrace	452	260,000.00	82,000.00
    Terrace	476	260,000.00	82,000.00
    Terrace	499	260,000.00	82,000.00
    Terrace	654	260,000.00	82,000.00
    Terrace	688	260,000.00	82,000.00
    Terrace	700	260,000.00	82,000.00
    Terrace	899	260,000.00	82,000.00
    Terrace	9901	260,000.00	82,000.00

  6. #6
    Join Date
    Oct 2003
    Posts
    357

    Wink

    Hi, can you post the expected outcome?

    Madhivanan

  7. #7
    Join Date
    Jun 2002
    Posts
    63
    aaaaaaaaa, Its ok, I;ve got it now, needed to correlate the inner query (sure I'd tried this before obviuosly not)

    Code:
    SELECT *
    FROM saleProperty AS p1
    WHERE price IN (SELECT max(price)
    FROM saleProperty AS p2
    WHERE p1.type=p2.type
    GROUP BY type)
    OR price IN (SELECT min(price)
    FROM saleProperty AS p2
    WHERE p1.type=p2.type
    GROUP BY type)
    was expecting output:

    Code:
    propertyID	ownerID	area	address1	address2	city	county	postCode	roomNo	bathRoomNo	receptionRoomNo	garage	type	freeOrLease	price	dateStarted	dateSold	notes
    321	3	Pontypridd	6	King Street	Upper Boat	Glamorgan	CF7 55R	3	2	1	1	Semi-detached	0	89,500.00	06/07/2003		
    56	8504	Pontypridd	4	Queen Anne Cresent	Treforest	Glamorgan	CF7 4RD	1	1	1	0	Terrace	0	82,000.00	12/03/2002	23/05/2002	damp?
    467	3954	Brecon	Brecon mansion	Nelson Avenue	Brecon	Brecon Beacons	CF37 1RE	5	2	2	0	Semi-detached	-1	258,000.00	06/12/2004		
    62	59	Pontypridd	Steel Farm	Cynon Street	Pontypridd	Glamorgan	CF35 T89	4	2	1	0	Terrace	0	260,000.00	28/11/2004		this is a nice house
    3	1631	Pontypridd	metal house	Golden Street	Pontypridd	Glamorgan	CF33 1RE	2	1	1	0	Detached	-1	95,000.00	14/12/2004		
    786	34	Merthyr Tydfil	34	Forest Road	Merthyr Tydfil	Glamorgan	CF32 5JT	6	3	2	2	Detached	0	598,000.00	28/11/2004		House is blue and red????? What were they thinking!
    Thanks for all your help

Posting Permissions

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