|
 |
access thread: select statement needed
Message #1 by "chris angus" <chris@a...> on Fri, 26 Oct 2001 22:37:08
|
|
I've got a table called prices
subkey psupp A1
fd731 ea 7.31
fd731 eagle 8.99
fd731 tx 7.87
i need to select the subkey psupp and A1 with the lowest price only
can anyone tell me how to do this and the syntax (to use in visual basic
6)
Message #2 by "Alan Douglas" <acdoug@s...> on Sat, 27 Oct 2001 05:24:32
|
|
SELECT SUBKEY, PSUPP, A1 FROM PRICES WHERE A1=MIN(A1);
> I've got a table called prices
>
> subkey psupp A1
> fd731 ea 7.31
> fd731 eagle 8.99
> fd731 tx 7.87
>
> i need to select the subkey psupp and A1 with the lowest price only
>
> can anyone tell me how to do this and the syntax (to use in visual basic
> 6)
Message #3 by "Ian Ashton" <ian@c...> on Sat, 27 Oct 2001 11:55:01 +0100
|
|
You cannot use and aggregate function (min) in a non-aggregating query.
Instead try the following:
SELECT Top 1 * FROM prices ORDER BY a1;
Ian Ashton
-----Original Message-----
From: Alan Douglas [mailto:acdoug@s...]
Sent: Saturday, October 27, 2001 5:25 AM
To: Access
Subject: [access] Re: select statement needed
SELECT SUBKEY, PSUPP, A1 FROM PRICES WHERE A1=MIN(A1);
> I've got a table called prices
>
> subkey psupp A1
> fd731 ea 7.31
> fd731 eagle 8.99
> fd731 tx 7.87
>
> i need to select the subkey psupp and A1 with the lowest price only
>
> can anyone tell me how to do this and the syntax (to use in visual basic
> 6)
Message #4 by "chris angus" <chris@a...> on Sun, 28 Oct 2001 00:14:54
|
|
sorry to bother you again but the following statement only returns one
record.
select top 1 * from prices order by a1
if i try top 100, it only returns the lowest 100 records
i need to return the lowest price for each supplier part
Message #5 by "chris angus" <chris@a...> on Sat, 27 Oct 2001 22:30:38
|
|
Thanks Ian, this works fine. although i don't understand why it selects
the lower price.
is there a way to ignore 0.00 values and select a genuine price.
thanks very much for your help
Message #6 by "Ian Ashton" <ian@c...> on Sun, 28 Oct 2001 09:40:24 -0000
|
|
Chris,
I am making a guess as to your extended data:
subkey psupp a1
fd731 ea 7.31
fd731 eagle 8.99
fd731 tx 7.87
fd732 ea 7.10
fd732 eagle 9.05
fd732 tx 8.00
If it were as above, then the following:
SELECT psupp, Min(a1) AS MinOfa1
FROM prices
GROUP BY psupp;
Returns:
psupp MinOfa1
ea 7.10
eagle 8.99
tx 7.87
I don't know if this is what you are after.
Ian Ashton
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Sunday, October 28, 2001 1:15 AM
To: Access
Subject: [access] Re: select statement needed
sorry to bother you again but the following statement only returns one
record.
select top 1 * from prices order by a1
if i try top 100, it only returns the lowest 100 records
i need to return the lowest price for each supplier part--
Message #7 by "Ian Ashton" <ian@c...> on Sun, 28 Oct 2001 12:32:21 -0000
|
|
Chris,
It selects the lowest price because of the Min(a1) function.
To ignore zero prices try:
SELECT subkey, Min(a1) AS minimum
FROM prices
GROUP BY subkey
HAVING Min(a1)<>"0";
Regards,
Ian
-----Original Message-----
From: chris angus [mailto:chris@a...]
Sent: Saturday, October 27, 2001 11:31 PM
To: Access
Subject: [access] Re: select statement needed
Thanks Ian, this works fine. although i don't understand why it selects
the lower price.
is there a way to ignore 0.00 values and select a genuine price.
thanks very much for your help
Message #8 by "chris angus" <chris@a...> on Sun, 28 Oct 2001 15:55:00
|
|
Ian you're dead right, just one thing though, it needs to return
Subkey Psupp A1
fd731 ea 7.31
fd732 ea 7.10
The reason is so I can export to a csv file and import into my business
app
Thanks for all your help
> Chris,
>
> I am making a guess as to your extended data:
>
> subkey psupp a1
> fd731 ea 7.31
> fd731 eagle 8.99
> fd731 tx 7.87
> fd732 ea 7.10
> fd732 eagle 9.05
> fd732 tx 8.00
>
>
> If it were as above, then the following:
>
> SELECT psupp, Min(a1) AS MinOfa1
> FROM prices
> GROUP BY psupp;
>
>
> Returns:
>
> psupp MinOfa1
> ea 7.10
> eagle 8.99
> tx 7.87
>
>
> I don't know if this is what you are after.
>
>
> Ian Ashton
>
>
>
> -----Original Message-----
> From: chris angus [mailto:chris@a...]
> Sent: Sunday, October 28, 2001 1:15 AM
> To: Access
> Subject: [access] Re: select statement needed
>
>
> sorry to bother you again but the following statement only returns one
> record.
>
> select top 1 * from prices order by a1
>
> if i try top 100, it only returns the lowest 100 records
>
> i need to return the lowest price for each supplier part--
|
|
|
View Cart
