CODEWITHSUNDEEP
sqlsql-server-2008datetime
User999999
User999999

Reputation: 2520

Select Top/bottom 2 values in single query ( /w subquery)

Consider following query:

SELECT personeelsnr,
  CAST(tijdstip AS DATE) AS Dag,
  MIN(tijdstip) AS MiddagIn,
  MAX(tijdstip) AS MiddagUit,
  DATEDIFF(MINUTE, MIN(tijdstip),MAX(tijdstip)) AS Middagpauze
FROM [STI].[DBO].[STI_Entry]
WHERE (tijdstip BETWEEN @DateFrom AND @DateTo
AND ((DATEPART(hh, tijdstip) BETWEEN 12 AND 13)
OR CAST(tijdstip AS TIME)        = '14:00:00'))
AND ( (personeelsnr              = @personeelsNr
AND (LEN(RTRIM(@leveranciersnr)) = 0
OR @leveranciersnr              IS NULL))
OR (indienstfirmanr              = @leveranciersnr
AND LEN(RTRIM(@leveranciersnr))  > 0) )
GROUP BY personeelsnr,
  naam,
  voornaam,
  CAST(tijdstip AS DATE) )

What i would like to achieve is instead of having MIN(tijdstip) as MiddagIn, MAX(tijdstip) as MiddagUit I would like to have the bottom 2 tijdstip

   e.g MIN(tijdstip) as MiddagIn, MIN(tijdstip) as MiddagUit
   where MiddagIn <> MiddagUit and MiddagIn < Middaguit

   For instance Person A has records for Day 1 as follows
   12:01:00
   12:07:00
   12:30:37
   12:57:00

my current solution gives me 12:01:00 (as MIN) and 12:57:00 (as MAX). But i want 12:01:00 (as MIN) and 12:07:00 (as MAX)

I have the knowledge to do this with a subquery, but i'm searching a proper solution without using a subquery. Could anyone point me in the right direction?

thnx everyone

Upvotes: 1

Views: 156

Answers (1)

cyan
cyan

Reputation: 747

For instance Person A has records for Day 1 as follows

12:01:00
12:07:00
12:30:37
12:57:00

my current solution gives me 12:01:00 (as MIN) and 12:57:00 (as MAX). But i want 12:01:00 (as MIN) and 12:07:00 (as MAX)

if your sample represent your data, try below sql:

with cte as
(
    select 
        ROW_NUMBER() over(order by a.tijdstip) as id
        ,a.tijdstip
    from @temp a
)
select 
    c.id, c.tijdstip as 'MIN',
    c2.tijdstip as 'Max'
from cte c left join cte c2 on c.id = c2.id-1
where (c.id % 2) <> 0

--RESULT
/*
id                   MIN             Max
-------------------- --------------- ---------------
1                    12:01:00        12:07:00
3                    12:30:37        12:57:00

*/

the ood row id will become the MIN and the even row id will become the MAX

Upvotes: 2

Related Questions