Select The Max And Second Max Value From A Select, And Group Them?

I've been battling with this problem for a week now and I need help. I have a select statement that generates a table showing every product, and an import date per product. How can

Solution 1:

You can query using row_number and pivot as below:

Select Product, [1] as [LastDate], [2] as [Last Before LastDate] from (
    Select top (2) with ties *, RowN=Row_Number() over(partitionby Product orderby ImportDate desc) from Products
        Orderby (Row_Number() over(partitionby Product orderby ImportDate desc) -1)/2+1
) a
pivot (max(ImportDate) for RowN in([1],[2])) p

Output using this query for your similar input as below:

+---------+------------+-----------------------+|Product|LastDate|LastBeforeLastDate|+---------+------------+-----------------------+|075031|2014-07-08|2014-06-19||075032|2014-12-09|NULL||075034|2016-03-10|2014-07-08||075036|2016-03-08|2015-09-08|+---------+------------+-----------------------+

Solution 2:

So, if I understand you correctly, you need the acIdent, then the latest import date, then the second to latest import date.

The difficult thing is the second to latest import date. For this, you need to exclude the latest import date.

This might work:

SELECT S.acIdent AS Product,
    (SELECTMAX(adDate) 
     FROM tHE_MoveItem MI2
     WHERE MI2.s.acIdent = MI.acIdent) AS LastImportDate,

    (SELECTMAX(adDate) 
     FROM tHE_MoveItem MI2
     WHERE MI2.acIdent = MI.acIdent
        AND MI2.ImportDate != (SELECTMAX(ImportDate) 
                                FROM tHE_MoveItem MI3
                                WHERE MI2.s.acIdent = MI3.acIdent)) 
            AS PreviousImportDate,
FROM tHE_Stock S
    INNERJOIN tHE_MoveItem MI
        ON s.acIdnet = MI.acIdent

Solution 3:

You can use rownumber instead of max, to find both the last and secondlast date.

Declare@mytabletable (productnr varchar(10), importdate date)
insertinto@mytablevalues
('075031', '2014-07-08'),
('075031', '2014-07-07'),
('075031', '2014-07-06'),

('075032', '2014-07-10'),
('075032', '2014-07-09'),
('075032', '2014-07-06')

;with products_cte as
(select productnr,importdate, ROW_NUMBER() over (partitionby productnr orderby importdate desc) rn
from@mytable
)

select t1.productnr, t1.importdate lastDate,t2.importdate SecondLastDate
from products_cte t1
leftjoin products_cte t2 on t1.productnr = t2.productnr and t2.rn =2where t1.rn =1

Solution 4:

One more option with lead and lag.

select productnr,importdate as lastdate,prev as secondlastdate
from (select productnr,importdate
      ,lead(importdate) over(partition by productnr orderby importdate) as nxt
      ,lag(importdate) over(partition by productnr orderby importdate) as prev
      from tbl
     ) t
where nxt is null   

lead on the date for last row (ordered by importdate per productnr) would be null and lag gives you the previous date before the lastdate.