RE: Select Top 3 Records

[allyne]

Hi Everyone,

I'm using SQL 7.0 and have a stored procedure that Selects the top 3 records. For example:

SELECT
Id = IDENTITY(int, 1, 1,),
[MIN],
Bid,
Market_City,
Market_State
[MOU]
INTO #3_1
FROM #3 As A
WHERE (Select count (*) FROM #3 WHERE [MIN] = a.[MIN] and [MOU]> a.[MOU])< 3

This works great and selects the top 3 records however I've come across records where the [MOU] is the same. For example:

Id [MIN] [MOU] Bid Market_City Market_State

1 222444 5 123 Norfolk VA
5 222444 5 123 Atlanta GA
3 111222 4 222 Seattle WA
4 222333 3 111 Tacoma WA

The results I'm getting is:

Id [MIN] [MOU] Bid Market_City Market_State

1 222444 5 123 Norfolk VA
5 222444 5 123 Atlanta GA
3 111222 4 222 Seattle WA

The results I want is:

Id [MIN] [MOU] Bid Market_City Market_State

1 222444 5 123 Norfolk VA
3 111222 4 222 Seattle WA
4 222333 3 111 Tacoma WA

I can't seem to get the correct syntax to take the above situation into consideration.

Any help would be much appreciated.

Thanks
Cathy

 

[jjonesal]

Is there a reason you didn't use a TOP clause with just a basic SELECT statement....

Seems like that would be an easier approach.. BOL has some good documentation on it... J. Jones
jjones@cybrtyme.com

 

[allyne]

Opps sorry for the confusion,

I need to find the Top 1 MOU(field name) for each MIN (field name) and not the Top 1 Record in the Table.

The results I'm getting is:

Id [MIN] [MOU] Bid Market_City Market_State

1 222444 5 123 Norfolk VA
5 222444 5 123 Atlanta GA
3 111222 4 222 Seattle WA

The results I want is:

Id [MIN] [MOU] Bid Market_City Market_State

1 222444 5 123 Norfolk VA
3 111222 4 222 Seattle WA
4 222333 3 111 Tacoma WA

Hope this is a little more clear. Let me know if this makes sense.

Cathy