Challenging SQL Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

From the STORES table, I need to select a set of 3 stores, then put them
into the
"SETS" table on 1 line, as store 1, store2, store3.

I've done this as an append query with 3 instances of STORES - STORES as E1,
STORES as E2, STORES as E3 and then SELECT E1.STORE as STORE1, E2.STORE AS
STORE2, E3.STORE AS STORE3.

Is there any way to only have one instance of STORES, select the set of 3
and then append them into SETS ?

Something like - SELECT record#1.STORE as STORE1,
record#2.STORE as STORE2,
record#3.STORE as STORE3
FROM ( SELECT TOP 3 STORE 3 STORE FROM STORES
I won't include all the conditions here)

Any suggestions would be appreciated.

Thanks,

Mike
 
Perhaps the first idea that comes to my mind is a rank and tally.

The "rank" consists of assigning values that can be grouped into different
slots (aka, the "tally").

In this case your rank will involve two numbers: one number that increments
once after every three rows, and a second that yields 1, 2, and 3 for each
row for a set of three. You can use division and modulo to yield these
required ranks.

The "rank" is typically a subquery, and this case is no exception.
Unfortunately, I don't have time to explicitly test a version for you, but
I'll give you the gist in air SQL, and perhaps another expert can flesh it
out, or I'll answer it better tomorrow.

"Rank" portion of a "Rank and Tally":

SELECT S.StorePrimaryKey, S.StoreName,

(SELECT Count(*) / 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS GroupOfThreeCounter,

(SELECT Count(*) mod 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS OneOfThreeWithinGroupCounter

FROM tblStores AS S

The idea is to gain something like this:

1 0 Store 1
1 1 Store 2
1 2 Store 3
2 0 Store 4
2 1 Store 5
2 2 Store 6
3 0 Store 7
3 1 Store 8
3 2 Store 9

Then take this "rank" and "tally" it in another query:

SELECT GroupOfThreeCounter,
MAX(iif([OneOfThreeWithinGroupCounter] = 0, [StoreName], null)) AS StoreA,
MAX(iif([OneOfThreeWithinGroupCounter] = 1, [StoreName], null)) AS StoreB,
MAX(iif([OneOfThreeWithinGroupCounter] = 2, [StoreName], null)) AS StoreC,
FROM ThatSubqueryIWroteAbove
GROUP BY GroupOfThreeCounter

AGAIN, this is AIR SQL. But the concept is rock solid.

Good luck. Let me know if you get it working. If not, I'll help out more
tomorrow.

David Atkins, MCP
 
Thanks for the response.

Haven't had time to try it, but had another idea -
I can do SELECT MIN(STR) as STORE1, MAX(STR) AS STORE2, but how would
I get the third store AS STORE3 ?

Mike


GreySky said:
Perhaps the first idea that comes to my mind is a rank and tally.

The "rank" consists of assigning values that can be grouped into different
slots (aka, the "tally").

In this case your rank will involve two numbers: one number that increments
once after every three rows, and a second that yields 1, 2, and 3 for each
row for a set of three. You can use division and modulo to yield these
required ranks.

The "rank" is typically a subquery, and this case is no exception.
Unfortunately, I don't have time to explicitly test a version for you, but
I'll give you the gist in air SQL, and perhaps another expert can flesh it
out, or I'll answer it better tomorrow.

"Rank" portion of a "Rank and Tally":

SELECT S.StorePrimaryKey, S.StoreName,

(SELECT Count(*) / 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS GroupOfThreeCounter,

(SELECT Count(*) mod 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS OneOfThreeWithinGroupCounter

FROM tblStores AS S

The idea is to gain something like this:

1 0 Store 1
1 1 Store 2
1 2 Store 3
2 0 Store 4
2 1 Store 5
2 2 Store 6
3 0 Store 7
3 1 Store 8
3 2 Store 9

Then take this "rank" and "tally" it in another query:

SELECT GroupOfThreeCounter,
MAX(iif([OneOfThreeWithinGroupCounter] = 0, [StoreName], null)) AS StoreA,
MAX(iif([OneOfThreeWithinGroupCounter] = 1, [StoreName], null)) AS StoreB,
MAX(iif([OneOfThreeWithinGroupCounter] = 2, [StoreName], null)) AS StoreC,
FROM ThatSubqueryIWroteAbove
GROUP BY GroupOfThreeCounter

AGAIN, this is AIR SQL. But the concept is rock solid.

Good luck. Let me know if you get it working. If not, I'll help out more
tomorrow.

David Atkins, MCP

MK said:
Hi all

From the STORES table, I need to select a set of 3 stores, then put them
into the
"SETS" table on 1 line, as store 1, store2, store3.

I've done this as an append query with 3 instances of STORES - STORES as E1,
STORES as E2, STORES as E3 and then SELECT E1.STORE as STORE1, E2.STORE AS
STORE2, E3.STORE AS STORE3.

Is there any way to only have one instance of STORES, select the set of 3
and then append them into SETS ?

Something like - SELECT record#1.STORE as STORE1,
record#2.STORE as STORE2,
record#3.STORE as STORE3
FROM ( SELECT TOP 3 STORE 3 STORE FROM STORES
I won't include all the conditions here)

Any suggestions would be appreciated.

Thanks,

Mike
Click to expand...
Click to expand...
 
With a subquery. Perhaps something like:

(SELECT MIN(STR) FROM [qryMain] qM WHERE qM.GroupID = [OuterQuery].GroupID
And [OuterQuery].STR > qM.STR) AS TheThirdStoreFoundInTheMiddleOfASetOfThree

I believe the issue is with your understanding of SQL concepts. I applaud
your desire to find alternatives. Once you dig into the how these subqueries
work, I truly believe it will "click" with you, as your curiosity is driving
you to know the "why."

-----
If you were going for a one-time cheesy method, I might suggest creating
three tables, and placing 1/3 of the total records in each table. Then add
an AutoNumber to these three tables, and link on the AutoNumber for your
append. But it doesn't feel like you're trying to find a cheesy solution --
rather I feel like you're trying to understand SQL.
-----

SQL is highly extensible when using VBA functions (in T-SQL for SQL Server
2000, functions perform a very similar role). You could theoretically solve
your issue using a function. For example, the query calls a function and
sends the store to the function. The function takes three stores, then
appends one record. Pretty easy actually.

-----

I'm sure I could think of other ideas if I kept going. There almost always
is more than one way to skin these cats. But often there is a "best
practice," and using the rank and tally I described earlier often qualifies
as the best pratice, *unless* you have hundreds of thousands or more records,
which then the function would qualify as best practice.

I hope this helps.

David Atkins, MCP


MK said:
Thanks for the response.

Haven't had time to try it, but had another idea -
I can do SELECT MIN(STR) as STORE1, MAX(STR) AS STORE2, but how would
I get the third store AS STORE3 ?

Mike


GreySky said:
Perhaps the first idea that comes to my mind is a rank and tally.

The "rank" consists of assigning values that can be grouped into different
slots (aka, the "tally").

In this case your rank will involve two numbers: one number that increments
once after every three rows, and a second that yields 1, 2, and 3 for each
row for a set of three. You can use division and modulo to yield these
required ranks.

The "rank" is typically a subquery, and this case is no exception.
Unfortunately, I don't have time to explicitly test a version for you, but
I'll give you the gist in air SQL, and perhaps another expert can flesh it
out, or I'll answer it better tomorrow.

"Rank" portion of a "Rank and Tally":

SELECT S.StorePrimaryKey, S.StoreName,

(SELECT Count(*) / 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS GroupOfThreeCounter,

(SELECT Count(*) mod 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS OneOfThreeWithinGroupCounter

FROM tblStores AS S

The idea is to gain something like this:

1 0 Store 1
1 1 Store 2
1 2 Store 3
2 0 Store 4
2 1 Store 5
2 2 Store 6
3 0 Store 7
3 1 Store 8
3 2 Store 9

Then take this "rank" and "tally" it in another query:

SELECT GroupOfThreeCounter,
MAX(iif([OneOfThreeWithinGroupCounter] = 0, [StoreName], null)) AS StoreA,
MAX(iif([OneOfThreeWithinGroupCounter] = 1, [StoreName], null)) AS StoreB,
MAX(iif([OneOfThreeWithinGroupCounter] = 2, [StoreName], null)) AS StoreC,
FROM ThatSubqueryIWroteAbove
GROUP BY GroupOfThreeCounter

AGAIN, this is AIR SQL. But the concept is rock solid.

Good luck. Let me know if you get it working. If not, I'll help out more
tomorrow.

David Atkins, MCP

MK said:
Hi all

From the STORES table, I need to select a set of 3 stores, then put them
into the
"SETS" table on 1 line, as store 1, store2, store3.

I've done this as an append query with 3 instances of STORES - STORES as E1,
STORES as E2, STORES as E3 and then SELECT E1.STORE as STORE1, E2.STORE AS
STORE2, E3.STORE AS STORE3.

Is there any way to only have one instance of STORES, select the set of 3
and then append them into SETS ?

Something like - SELECT record#1.STORE as STORE1,
record#2.STORE as STORE2,
record#3.STORE as STORE3
FROM ( SELECT TOP 3 STORE 3 STORE FROM STORES
I won't include all the conditions here)

Any suggestions would be appreciated.

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
Thanks again.

Won't have time to pursue right now, but it's at the top of my "wish" list
when things ease up. Will post the results.

Mike


GreySky said:
With a subquery. Perhaps something like:

(SELECT MIN(STR) FROM [qryMain] qM WHERE qM.GroupID = [OuterQuery].GroupID
And [OuterQuery].STR > qM.STR) AS TheThirdStoreFoundInTheMiddleOfASetOfThree

I believe the issue is with your understanding of SQL concepts. I applaud
your desire to find alternatives. Once you dig into the how these subqueries
work, I truly believe it will "click" with you, as your curiosity is driving
you to know the "why."

-----
If you were going for a one-time cheesy method, I might suggest creating
three tables, and placing 1/3 of the total records in each table. Then add
an AutoNumber to these three tables, and link on the AutoNumber for your
append. But it doesn't feel like you're trying to find a cheesy solution --
rather I feel like you're trying to understand SQL.
-----

SQL is highly extensible when using VBA functions (in T-SQL for SQL Server
2000, functions perform a very similar role). You could theoretically solve
your issue using a function. For example, the query calls a function and
sends the store to the function. The function takes three stores, then
appends one record. Pretty easy actually.

-----

I'm sure I could think of other ideas if I kept going. There almost always
is more than one way to skin these cats. But often there is a "best
practice," and using the rank and tally I described earlier often qualifies
as the best pratice, *unless* you have hundreds of thousands or more records,
which then the function would qualify as best practice.

I hope this helps.

David Atkins, MCP


MK said:
Thanks for the response.

Haven't had time to try it, but had another idea -
I can do SELECT MIN(STR) as STORE1, MAX(STR) AS STORE2, but how would
I get the third store AS STORE3 ?

Mike


GreySky said:
Perhaps the first idea that comes to my mind is a rank and tally.

The "rank" consists of assigning values that can be grouped into different
slots (aka, the "tally").

In this case your rank will involve two numbers: one number that increments
once after every three rows, and a second that yields 1, 2, and 3 for each
row for a set of three. You can use division and modulo to yield these
required ranks.

The "rank" is typically a subquery, and this case is no exception.
Unfortunately, I don't have time to explicitly test a version for you, but
I'll give you the gist in air SQL, and perhaps another expert can flesh it
out, or I'll answer it better tomorrow.

"Rank" portion of a "Rank and Tally":

SELECT S.StorePrimaryKey, S.StoreName,

(SELECT Count(*) / 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS GroupOfThreeCounter,

(SELECT Count(*) mod 3 FROM tblStores S1 WHERE S1.StorePrimaryKey <=
S.StorePrimaryKey) AS OneOfThreeWithinGroupCounter

FROM tblStores AS S

The idea is to gain something like this:

1 0 Store 1
1 1 Store 2
1 2 Store 3
2 0 Store 4
2 1 Store 5
2 2 Store 6
3 0 Store 7
3 1 Store 8
3 2 Store 9

Then take this "rank" and "tally" it in another query:

SELECT GroupOfThreeCounter,
MAX(iif([OneOfThreeWithinGroupCounter] = 0, [StoreName], null)) AS StoreA,
MAX(iif([OneOfThreeWithinGroupCounter] = 1, [StoreName], null)) AS StoreB,
MAX(iif([OneOfThreeWithinGroupCounter] = 2, [StoreName], null)) AS StoreC,
FROM ThatSubqueryIWroteAbove
GROUP BY GroupOfThreeCounter

AGAIN, this is AIR SQL. But the concept is rock solid.

Good luck. Let me know if you get it working. If not, I'll help out more
tomorrow.

David Atkins, MCP

:


Hi all

From the STORES table, I need to select a set of 3 stores, then put them
into the
"SETS" table on 1 line, as store 1, store2, store3.

I've done this as an append query with 3 instances of STORES - STORES as E1,
STORES as E2, STORES as E3 and then SELECT E1.STORE as STORE1, E2.STORE AS
STORE2, E3.STORE AS STORE3.

Is there any way to only have one instance of STORES, select the set of 3
and then append them into SETS ?

Something like - SELECT record#1.STORE as STORE1,
record#2.STORE as STORE2,
record#3.STORE as STORE3
FROM ( SELECT TOP 3 STORE 3 STORE FROM STORES
I won't include all the conditions here)

Any suggestions would be appreciated.

Thanks,

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top