Need help with an SQL query

[DnetMHZ]

I have a table called lets say Table1 with the following columns

ItemID, Priority, Amount


The same ItemID may have multiple entries in the table each with a different priority #
The amount column is a number representing a percentage 0.00 through 100.00


What I need to do is list the ItemIDs where the lowest priority has an amount of 100
and another entry exists for that itemID


Here is some sample data. If the query works correctly it should return ItemID's 5 & 7.


ItemID.....Priority.....Amount
5..........1............100
5..........2............20
6..........1............50
6..........2............50
7..........5............100
7..........7............40

 

[Jeraden]

First part gets all rows with Amount=100, which is required.
First subselect makes sure no other rows exist for that ItemID with a lower priority (since the 100 row must have the lowest priority)
Second subselect makes sure at least one other row exists with a higher priority (since you need another entry for that itemID)

 

[DnetMHZ]

Originally posted by: Jeraden
First part gets all rows with Amount=100, which is required.
First subselect makes sure no other rows exist for that ItemID with a lower priority (since the 100 row must have the lowest priority)
Second subselect makes sure at least one other row exists with a higher priority (since you need another entry for that itemID)

Your query looks like it should work but for some reason I'm getting
"The column prefix 'b' does not match with a table name or alias name used in the query."
Is it because 'b' is being passed from one subselect to another?

<edit>

Think I found it... the last line should read

and C.priority > a.priority) .. right?

 

[Jeraden]

yeah, my bad, forgot to change the alias in that last part