Brain Teaser

Archived Topics
1

Is a nested query possible?

First part of the query would need to select distict sequence numbers from a production schedule (threre are mulitples).

ex. select distinct(seqno) from schedule;

The second part would need to count the number of different models (represented by the 30th digit of the model number) on the schedule.

ex select mid(model,30,1), count(mid(model,30,1)) from schedule group by mid(model,30,1);

On the schedule there are several duplicate sequence numbers, and each sequence number always has the same model number.

I tried to attach a sample file, but when I do the attachment my posting will not submit, so I pasted some lines from notepad in the comment field.

2

DB2,

Because each seqno has the same model number, I don’t think you need a nested query. You could try something like this:SELECT distinct(seqno), model, count(model) FROM schedule s GROUP BY seqno, mid(model, 30,1) and that will give you a list of all the sequences, what the model number is, and the count of it. Notice the GROUP BY clause; it is grouped by the seqno then model, so each seqno is grouped by the 30th digit of the model. To test this, I used the following with mid(model,2,1)

[code]My table:
ndx, seqno, model
1, 1, 11 <-same mid(model,2,1)
2, 1, 11 <-same mid(model,2,1)
3, 2, 12
4, 3, 14
5, 3, 14
6, 4, 41 <- same mid(model,2,1), different seqno

results:
seqno, model, count(model)
1, 11, 2
2, 12, 1
3, 14, 2
4, 41, 1[/code]

3

This data would be a better representation:

ndx, seqno, model
1 567 A
2 567 A
3 568 A
4 568 A
5 569 C
6 569 C
7 570 B
8 570 B
9 571 A
10 572 C
11 573 B

I need the output to read:

Model Qty
A 3
B 2
C 3

4

First off, yes, there is a concept of “nested queries”… but the more well known term is “subquery”.

Generally the argument goes that anything you want to do with subqueries can be done more efficiently without them. But heck, who cares if it makes the query simpler and runs within a reasonable amount of time. Therefore, what about the following…

SELECT t.model, count(t.model) as qty FROM (select distinct seqno, mid(model,30,1) as model from schedule) t group by t.model;

See, you perform subqueries by wrapping up a query in parentheses. So, in this case, we doing the query we want for model and count against the result of a query. We’ve named the subquery result ‘t’. Now we can just pretend like we have a table named ‘t’ and do any query we want against it.

Hope that helps, please let me know if I should clarify something a bit more.

Regards,

5

Brilliant, works great.