SQL Max Group By Query Help

Question

Hi All, I have a quick question. How do I select the two values I need in one query? Currently I'm doing this, which works fine, but it's obviously running two queries when one should do the trick. I tried MAX(columnA) and GROUP BY ColumnB, but that returns multiple row. I only want one row returned.

DECLARE @biID  bigint  
, @dtThreshold      DateTime 

  SELECT @biID = MAX(biID)
FROM tbPricingCalculationCount WITH (NOLOCK)

  SELECT @dtThreshold = dtDateTime
FROM tbPricingCalculationCount WITH (NOLOCK)
WHERE biID = @biID

I would like both those variables to be set correctly in one query. How can I do that?

Thanks, ~ck

Answer 1

can you not just do this?

SELECT TOP 1 @biID = biID, @dtThreshold = dtDateTime 
  FROM tbPricingCalculationCount WITH (NOLOCK) 
ORDER BY biID DESC;

Answer 2

How about

SELECT TOP 1 @biID = biID, @dtThreshold = dtDateTime
FROM tbPricingCalculationCount (NOLOCK)
ORDER BY biID desc

Answer 3

This returns dtDateTime for the row with the largest biID:

SELECT t1.dtDateTime
FROM tbPricingCalculationCount t1
LEFT JOIN tbPricingCalculationCount t2
ON t2.biID > t1.biID
WHERE t2.biID IS NULL

If more than one row shares the same "largest" biID, then you need to limit the results to one using TOP:

SELECT TOP 1 t1.dtDateTime
FROM tbPricingCalculationCount t1
LEFT JOIN tbPricingCalculationCount t2
ON t2.biID > t1.biID
WHERE t2.biID IS NULL

Answer 4

How about:

DECLARE
  @biID bigint,
  @dtThreshold DateTime 

SELECT
  @dtThreshold = A.dtDateTime,
  @biID = B.biID
FROM tbPricingCalculationCount A
  INNER JOIN (SELECT MAX(biID) biID
              FROM tbPricingCalculationCount) B
    ON A.biID = B.biID

If you're not using the biID elsewhere, you can even trim it to:

DECLARE
  @dtThreshold DateTime 

SELECT
  @dtThreshold = A.dtDateTime
FROM tbPricingCalculationCount A
  INNER JOIN (SELECT MAX(biID) biID
              FROM tbPricingCalculationCount) B
    ON A.biID = B.biID