0

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.

Daniil
  • 87
  • 1
  • 12
  • Why is this question not about the error that "you do no understand" ? This is in fact OK, because that is answered [What is the error "Every derived table must have its own alias" in MySQL?](https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql). I hope this solves the error, and changes the nature of this question. – Luuk Apr 16 '22 at 13:13
  • ERROR 1248 (42000) first query add alias(table name) to your query. – RF1991 Apr 16 '22 at 13:19
  • 1
    Can you explain the difference between "find maximal value count for prop in table" and the minimum value of the same count ? – Luuk Apr 16 '22 at 13:25
  • @Luuk thank you for the link. The query (Q1) now works as was initially expected. – Daniil Apr 16 '22 at 17:22
  • @RF1991 The link provided by Luuk explained what exactly this error meant. Now it's been fixed. – Daniil Apr 16 '22 at 17:22
  • The main question (1) is what is worrying me. Are there ways of "SELECT-looping" or no? Thank you all for your attention! – Daniil Apr 16 '22 at 17:22
  • @Luuk thank you, now I see that I should've expressed clearer. By "find maximal value count for prop in table" I meant "find maximal count of repetitions in the column among all values of the column". Now I'll fix it. – Daniil Apr 16 '22 at 17:48

1 Answers1

1

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

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • It is an interesting trick with LIMIT 1! Thank you! But still, can we iterate in a SELECT-query without grouping? Because (at least theoretically) it may prove to be (not the only possible way but still) helpful! – Daniil Apr 16 '22 at 19:09
  • 1
    I added an option without `GROUP BY` – Luuk Apr 16 '22 at 19:18
  • Thank you very much for the code samples! I see that they're what I need! But now I am going to dig the information about all the functions you've used! – Daniil Apr 18 '22 at 11:32