user5226582 - 10 months ago 34

SQL Question

Lets say I have a table of this form:

`ID value`

1 2

2 1

3 4

What's the

`value`

The desired output of the query is

`ID`

1

1

2

3

3

3

3

Answer

If you have a numbers table, you can do:

```
select t.id
from t join
numbers n
on n.n <= t.value;
```

There are various ways to construct a numbers table. Here is a brute force way that works in most databases:

```
select t.id
from t join
(select 1 as n union all select 2 union all select 3) n
on n.n <= t.value;
```

Source (Stackoverflow)