Get second maximum number only if highest one not available in SQL and not return null value

Question

My Table:

<table >
  <tr>
    <th>ProductCode</th>
    <th>year_numb</th>
    <th>month_numb</th>
    <th>qty_instock</th>
  </tr>
  <tr>
    <td>002792</td>
    <td>2020</td>
    <td>3</td>
    <td>5</td>
  </tr>
  <tr>
    <td>002792</td>
    <td>2020</td>
    <td>4</td>
    <td>1</td>
  </tr>
  <tr>
    <td>002792</td>
    <td>2020</td>
    <td>5</td>
    <td>4</td>
  </tr>
  <tr>
    <td>000002</td>
    <td>2020</td>
    <td>3</td>
    <td>4</td>
  </tr>
    <tr>
    <td>000002</td>
    <td>2020</td>
    <td>4</td>
    <td>10</td>
  </tr>
</table>

SQL:

strSql1 = "SELECT qty_instock FROM StockMaster WHERE ProductCode = '" + strProdCode + "' AND month_numb = (SELECT MAX(month_numb) from StockMaster)"

This one only return values with the highest month_number which is '5' for this matter however '000002' only returns a null value. please give me a solution to display second highest number from month_number.

Needed output:

<table >
  <tr>
    <th>ProductCode</th>
    <th>year_numb</th>
    <th>month_numb</th>
    <th>qty_instock</th>
  </tr>
 
  <tr>
    <td>002792</td>
    <td>2020</td>
    <td>5</td>
    <td>4</td>
  </tr>
    <tr>
    <td>000002</td>
    <td>2020</td>
    <td>4</td>
    <td>10</td>
  </tr>
</table>

Do you want to get max `qty_instock` for given `ProductCode`? For example, for `ProductCode = 002792` return `5`, and for `ProductCode = 000002` return `10`? — Iliar Turdushev, May 15 '20 at 07:11

score 0 · Answer 1 · answered May 15 '20 at 07:04

You can try below query to get the desired result -

Sample Table & Data

Declare @Product table 
(ProductCode varchar(10), [Year] int, month_numb int, qty_instock int)

insert into  @Product
values
('002792',  2020  ,  4 ,  1 ),
('002792',  2020  ,  5 ,  1 ),
('000002',  2020  ,  3 ,  13),
('000002',  2020  ,  4 ,  5 )

Query

;With cte(ProductCode, [Year], month_numb )
as
(select ProductCode, [Year], max(month_numb) month_numb
from @Product p
group by ProductCode, [Year]
) 
select p.* from @Product p 
inner join cte on p.ProductCode = cte.ProductCode 
and p.Year = cte.Year
and p.month_numb = cte.month_numb

score 0 · Accepted Answer · answered May 18 '20 at 03:20

If you need to get max qty_instock for given product code strProdCode then you should use the next query:

select top 1 qty_instock
from StockMaster
where ProductCode = strProdCode
order by month_numb desc;

This query for strProdCode = '002792' returns value 4, and for value strProdCode = '000002' returns 10. Here is complete sample that demostrates this query.

As a side note, if you are going to use this query in you C# code then it is recommended to use parameters in SQL statemets instead of directly inlining values into the query. This post describes why it is important to use parameters.

Get second maximum number only if highest one not available in SQL and not return null value

2 Answers2