SELECT help

[Guest_imported]

Hi there!

I'd like to know how can I solve my problem (using SELECT statement? Or..?)

I have a Table with columns ID, Position, and Number (where ID is an ID of an item, Position is a name of a place and Number describes how many items with that ID I have on that Position). Now, a certain number of items with a certain ID can be placed on many Positions .
Problem: If an item is placed on more than one position, i'd like to find a Position, where there's a maximum number of that particular item; and I want to do that for all items that are placed on more than one place.

Example:

Table:

ID Position Number
------------------------------------
1 1 10
1 2 1
1 5 15
2 3 1
2 2 4
3 5 1

Desired output of a SELECT query:

ID Position
-------------------
1 5
2 2

Thanks!

 

[tlbroadbent]

You can use a correlated sub-query.

Select ID, Position
From Table_Name As t
Where Number =
(Select Max(Number) From Table_Name
Where ID=t.ID) Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Guest_imported]

Thank You!

it works just fine... ;-)

T.