title: Get other columns that corresponds to MIN and MAX value of one column in SQL Server 2008
body: <p>I've a situation, where I need to get both min() and max() of column and one more column that corresponds to min() and max() functions. I've tried the query using INNER JOIN, it was successful when using either min() or max(). When both are used nothing returned.</p>
  
  <pre><code>SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
  , MN.Demand MinDemand, MN.TIME MinDemandAt
  FROM 
  (
      SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
  ) A
  INNER JOIN 
  (
      SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
      STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
      ORDER BY Demand
  ) MN
  ON A.IslandCode = MN.IslandCode AND A.LogTime = MN.LogTime AND A.Demand = MN.Demand
  </code></pre>
  
  <p>the above query returns following result, that is correct</p>
  
  <pre><code>IslandCode  LogTime     MinDemand       MinDemandAt
  1           2013-08-13  698         13:0
  </code></pre>
  
  <p>query for max()</p>
  
  <pre><code>SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
  , MX.Demand MaxDemand, MX.TIME MaxDemandAt
  FROM 
  (
      SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
  ) A
  INNER JOIN
  (
      SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
      STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
      ORDER BY Demand DESC
  ) MX
  ON A.IslandCode = MX.IslandCode AND A.LogTime = MX.LogTime AND A.Demand = MX.Demand
  </code></pre>
  
  <p>returns, this is also correct</p>
  
  <pre><code>IslandCode  LogTime       MaxDemand     MaxDemandAt
  1           2013-08-13  1463            20:0
  </code></pre>
  
  <p>but when both the queries are used in same query, returns nothing</p>
  
  <pre><code>SELECT A.IslandCode, CONVERT(DATE, A.LogTime)LogTime
  , MX.Demand MaxDemand, MX.TIME MaxDemandAt
  , MN.Demand MinDemand, MN.TIME MinDemandAt
  FROM 
  (
      SELECT IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
  ) A
  INNER JOIN
  (
      SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
      STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
      ORDER BY Demand DESC
  ) MX
  ON A.IslandCode = MX.IslandCode AND A.LogTime = MX.LogTime AND A.Demand = MX.Demand
  INNER JOIN 
  (
      SELECT TOP 1 IslandCode, CONVERT(DATE, LogTime) LogTime, SUM(KiloWatt) Demand, 
      STR(DATEPART(HOUR, LogTime)) + ':' + LTRIM(STR(DATEPART(MINUTE, LogTime))) TIME
      FROM ph_MachineDailyReadings 
      WHERE IslandCode = 1 AND CONVERT(DATE, LogTime) = '2013-08-13'
      GROUP BY IslandCode, LogTime
      ORDER BY Demand
  ) MN
  ON A.IslandCode = MN.IslandCode AND A.LogTime = MN.LogTime AND A.Demand = MN.Demand
  </code></pre>
  
  <p>expected result</p>
  
  <pre><code>IslandCode   LogTime     MinDemand  MinDemandAt   MaxDemand   MaxDemandAt
      1       2013-08-13  1463        20:0        698      13:0
  </code></pre>
  
  <p>can i get this result in single query or do i need to go for a stored procedure based solution?</p>
  
  <p>thanks in advance</p>