Limiting Query Results

[Guest]

Have two tables. Table1 lists the number of stores per state that I need. Table2 lists all the available stores

I need to Select Distinct T2.StoreID, limiting the number of results to T1.Need where T1.State = T2.State

Example below. Can't figure out the SQL for this. Any ideas

Thanks

Mik

Table1

State Need
------ -----
NY

Table

State StoreID
----- ----------
NY 5
NY 5
NY 5
NY 5
NY 5

 

[Brian Camire]

If there are more stores in a given state than you need, how do you decide
which ones to select? Assuming, you want to select the stores in ascending
order of StoreID, the StoreID in Table2 is unique, and the State in Table1
is unique, you might try a query whose SQL looks something like this:

SELECT
[Table2].*
FROM
[Table2]
INNER JOIN
[Table1]
ON
[Table2].[State] = [Table1].[State]
WHERE
(SELECT
Count(*)
FROM
[Table2] AS [Self]
WHERE
[Self].[State] = [Table2].[State]
AND
[Self].[StoreID] <= [Table2].[StoreID]) <= [Table1].[Need]

Have two tables. Table1 lists the number of stores per state that I need.

Table2 lists all the available stores.

I need to Select Distinct T2.StoreID, limiting the number of results to

T1.Need where T1.State = T2.State.