How to formulate FOR EACH loop in SQL?

Question

I am developing a SQL query that currently works for one group/mode combination. But I want this query to work for all groups and modes. And include each combination one time only. But in source data there are many other columns, so there is more than one record per combination.

So my source table structure is:

group   mode   p-val
-----   ----   ------
A       B      4.567
C       D      3.694

How can I setup a loop to loop through each group/mode combination? And I'd rather not use cursors!

Please format your posts correctly as previously requested. What is the loop doing on each step? If it is something inherently procedural then there would be no particular reason to use another looping construct rather than cursors (as they are specifically designed for looping through rows of results). Best would be to rewrite it in a set based way though but you have given us precisely zero information to go off here. — Martin Smith
– Martin Smith, Commented May 12, 2011 at 22:31

Philip Kelley · Accepted Answer · 2011-05-12 22:39:24Z

2

A very broad and general question. Here's a broad and general answer.

First, write a query to extract the set of the values you are interested in, such that each set is unique. SELECT DISTINCT... and SELECT... GROUP BY... suggest themselves.

This data could be stored as a temp table, included as a subquery, or just made part of the overall final query. The best to use depends entirely on what you're working on.

Next, write your "main" processing query using this subset as a basis. Something like ...FROM <theSubset> INNER JOIN <etc>.

Done properly (I'm not saying it'd be easy), the effects of this set-based methodology are quite similar to the results achieved with procedural looping.

answered May 12, 2011 at 22:39

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

salvationishere Over a year ago

I apologize for the generalness of my question. But the problem is difficult to explain fully. However, I have fixed this problem using set-based vs. cursor approach now! It took alot of work, but I was able to loop through a WHILE loop to implement this logic

ahmet alp balkan · Accepted Answer · 2011-05-12 22:38:07Z

2

This is called JOIN in sql. i.e. the statement

SELECT * FROM table AS a JOIN table AS b;

results

a | b | 4.5   | a | b | 4.5
a | b | 4.5   | c | d | 3.694
c | d | 3.694 | a | b | 4.5
c | d | 3.694 | c | d | 3.694

now you have all combinations of [group,mode] records. Read more on JOINs from Google.

answered May 12, 2011 at 22:38

ahmet alp balkan

45.9k43 gold badges152 silver badges224 bronze badges

Collectives™ on Stack Overflow

How to formulate FOR EACH loop in SQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related