CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
JohnN
JohnN

Reputation: 1010

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 Numbercolumn. 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 |

Upvotes: 0

Views: 38

Answers (3)

neer
neer

Reputation: 4082

Try this

SELECT
    FileName, 
    Category, 
    Value, 
    Number
FROM   
    TABLE T
WHERE
    T.Number IN 
    ( 
        SELECT DISTINCT TOP 2 Number 
        FROM Table IT
        WHERE
            IT.FileName  = T.FileName AND
            IT.Category = T.Category    
        ORDER BY IT.Number DESC
    )

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

You can use a subquery to get the 2 largest, distinct numbers:

select FileName, Category, Value, Number
from   Table
where  Number in (SELECT DISTINCT TOP 2 Number FROM Table ORDER BY Number desc);

Upvotes: 0

Caius
Caius

Reputation: 243

Use a subquery to find the max number

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

Upvotes: 2

Related Questions