SQL: FOR Loop in SELECT query-CodePudding

Is there a way to go through a FOR LOOP in a SELECT-query? (1)

I am asking because I do not know how to commit in a single SELECT-query collection of some data from table t_2 for each row of table t_1 (please, see UPDATE for an example). Yes, it's true that we can GROUP BY a UNIQUE INDEX but what if it's not present? Or how to request all rows from t_1, each concatenated with a specific related row from t_2. So, it seems like in a Perfect World we would have to be able to loop through a table by a proper SQL-command (R). Maybe, ANY(...) will help?

Here I've tried to find maximal count of repetitions in column prop among all values of the column in table t. I.e. I've tried to carry out something alike Pandas' t.groupby(prop).max() in an SQL query (Q1):

SELECT Max(C) FROM   (SELECT Count(t_1.prop) AS C
                      FROM   t AS t_1
                      WHERE  t_1.prop = ANY (SELECT prop
                                             FROM   t AS t_2));

But it only throws the error:

Every derived table must have its own alias.

I don't understand this error. Why does it happen? (2)

Yes, we can implement Pandas' value_counts(...) way easier by using SELECT prop, COUNT() GROUP BY prop. But I wanted to do it in a "looping" way staying in a "single non-grouping SELECT-query mode" for reason (R).

This sub-query, which attempts to imitate Pandas' t.value_counts(...)) (Q2):

SELECT Count(t_1.prop) AS C FROM t AS t_1 WHERE t_1.prop = ANY(SELECT prop FROM t AS t_2)

results in 6, which is simply a number of rows in t. The result is logical. The ANY-clause simply returned TRUE for every row and once all rows had been gathered COUNT(...) returned simply the number of the gathered (i.e. all) rows.

By the way, it seems to me that in the "full" previous SELECT-query (Q1) should return that very 6.

So, the main question is how to loop in such a query? Is there such an opportunity?

UPDATE

The answer to the question (2) is found here, thanks to Luuk. I just assigned an alias to the (...) subquery in SELECT Max(C) FROM (...) AS sq and it worked out. And of course, I got 6. So, the question (1) is still unclear.

I've also tried to do an iteration this way (Q3):

SELECT (SELECT prop_2 FROM t_2 WHERE t_2.prop_1 = t_1.prop) AS isq FROM t_1;

Here in t_2 prop_2 is connected to prop_1 (a.k.a. prop in t_1) as many to one. So, along the course, our isq (inner select query) returns several (rows of) prop_2 values per each prop value in t_1. And that is why (Q3) throws the error:

Subquery returns more than 1 row. Again, logical. So, I couldn't create a loop in a single non-grouping SELECT-query.

CodePudding user response：

This query will return the value for b with the highest count:

SELECT b, count(*)
FROM table1
GROUP BY b
ORDER BY count(*) DESC
LIMIT 1;

see: DBFIDDLE

EDIT: Without GROUP BY

SELECT b,C1
FROM (
   SELECT
      b,
      ROW_NUMBER() OVER (PARTITION BY B ORDER BY A) C1,
      ROW_NUMBER() OVER (PARTITION BY B ORDER BY A DESC) C2
  FROM table1
  ) x
WHERE x.C2=1

see: DBFIDDLE