David David - 4 months ago 7
SQL Question

The results of two queries into one table

I have a SQL table that looks something like this:

| FileName | Category | Value | Number |
|:---------:|:--------:|:-----:|:------:|
| TAG File1 | First | 10 | 1 |
| TAG File1 | Second | 8 | 1 |
| TAG File1 | Third | 4 | 1 |
| TAG File2 | First | 13 | 1 |
| TAG File2 | Second | 5 | 1 |
| TAG File2 | Third | 6 | 1 |
| TAG File1 | First | 11 | 2 |
| TAG File1 | Second | 7 | 2 |
| TAG File1 | Third | 5 | 2 |
| TAG File2 | First | 14 | 2 |
| TAG File2 | Second | 6 | 2 |
| TAG File2 | Third | 5 | 2 |
| TAG File1 | First | 10 | 3 |
| TAG File1 | Second | 6 | 3 |
| TAG File1 | Third | 5 | 3 |
| TAG File2 | First | 12 | 3 |
| TAG File2 | Second | 7 | 3 |
| TAG File2 | Third | 4 | 3 |
| TAG File1 | First | 11 | 4 |
| TAG File1 | Second | 8 | 4 |
| TAG File1 | Third | 5 | 4 |
| TAG File2 | First | 13 | 4 |
| TAG File2 | Second | 5 | 4 |
| TAG File2 | Third | 5 | 4 |


I wanted to write a query that will only show the results for the two "most recent" values in the
Number
column. The number column is a counting value. Everytime this table is updated with a new set of data, the value in the
Number
column for that set of data is +1 from the max value. Ultimately, I want a query that would accomplish what this query would.

select FileName, Category, Value, (select max(Number) from Table) as Number
from Table;


while also having these results in the table as well:

select FileName, Category, Value, (select max(Number)-1 from Table) as Number
from Table;


The results should look something like this:

| FileName | Category | Value | Number |
|:---------:|:--------:|:-----:|:------:|
| TAG File1 | First | 10 | 3 |
| TAG File1 | Second | 6 | 3 |
| TAG File1 | Third | 5 | 3 |
| TAG File2 | First | 12 | 3 |
| TAG File2 | Second | 7 | 3 |
| TAG File2 | Third | 4 | 3 |
| TAG File1 | First | 11 | 4 |
| TAG File1 | Second | 8 | 4 |
| TAG File1 | Third | 5 | 4 |
| TAG File2 | First | 13 | 4 |
| TAG File2 | Second | 5 | 4 |
| TAG File2 | Third | 5 | 4 |

Answer

Use a subquery to find the max number

SELECT * FROM table WHERE number >= (SELECT MAX(number) FROM table) - 1