|
|
Posted by mazzarin on 10/02/34 11:50
A bit of clarification on my problem
If I just do a straight SQL distinct on my select statement, it does
what I want when you get down to it, but it completely destroys the
organization of the table. The part numbers were entered in a certain
manner and I do not believe they can be reorganized through any typical
sort. For example A-105A is higher then A-400B, but if you sorted in
Excel (for example) it would put 105 below 400
mazzarin@gmail.com wrote:
> I am trying to generate some datasets with some queries...
>
> With a given series information, it should return PART_NOs that has STD
> = 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
>
> DB examples below:
>
> Main DB
>
> ID PART_NO SERIES STD
> 1 A-1 A 1
> 2 A-2 A 1
> 3 A-3 A 1
> 4 D-1 D 1
> 5 D-2 D 0
>
> Price DB
>
> ID PART_ID TYPE START PRICE
> 50 1 X 1000 50
> 51 1 X 10000 40
> 52 1 Y 1000 60
> 53 1 Y 10000 50
> 54 2 X 1000 50
> 55 2 X 10000 40
> 56 2 Y 1000 60
> 57 2 Y 10000 50
> 58 2 X 1000 90
>
> etc.
>
> main.ID and Price.PART_ID are paired together.
>
>
>
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
>
> PART_NO
> A-1 1000 50
> A-1 10000 40
> A-3 1000 90
>
> Note how it skipped printing A2 because the price is the same as A1.
>
>
>
> I'm really looking for the SQL code here... I can't get it to filter on
> distinct price.
>
> SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> FROM MAIN, PRICING
> WHERE (MAIN.SERIES LIKE 'A')
> AND (MAIN.STD = '1')
> AND (PRICING.PRICE != '')
> AND (PRICING.TYPE = 'X')
> AND (MAIN.ID = PRICING.PART_ID)
>
> I've been trying to use GROUP BY and HAVING to get what I need but it
> doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> can use the SQL DISTINCT command...? If I try and use it in my WHERE
> statement it gives me syntax errors, from what I understand you can
> only have distinct in the select statement? I'm not sure how to
> integrate that into the query to suit my needs.
>
> Thanks for any help.
Navigation:
[Reply to this message]
|