Multiple columns for AS-keyword & next-latest row

I have a number of rows whose audits are stored in a separate table. Using one select, I want to acquire LatestState, LatestDate, PreviousState and PreviousDate. I honestly don't know where to begin to formulate the T-SQL query for this, but I'm guessing that I'd need to make two sub-queries inside the SELECT (pseudo-pseudo-code):

SELECT 
   (SELECT [State], [Date] FROM Audits WHERE HighestID) AS ([LatestState], [LatestDate])
   (SELECT [State], [Date] FROM Audit开发者_JAVA技巧s WHERE NextHighestID) AS ([PreviousState], [PreviousDate])
FROM [Data]

Is this possible? Thank you!

EDIT I've resolved this by using joins:

SELECT ps1.Rating, ps1.DatePerformed, ps2.Rating AS PreviousRating, ps2.DatePerformed AS PreviousDate
FROM [Data]
LEFT JOIN [Audit] ps1 ON ps1.[ID] = (SELECT MAX(ID) FROM [Audit] WHERE [ProjectID] = p.[ProjectID])
LEFT JOIN [Audit] ps2 ON ps2.[ID] = (SELECT MAX(ID) FROM [Audit] WHERE [ProjectID] = p.[ProjectID] AND ID < ps1.ID)

Thank you for your input!

This will give you the information in two rows. First row is latest and second row is previous :).

select top (2) [State], [Date]
from Audits
order by ID desc

If you want them in one row you could try this.

;with cte as
(
  select top 2 [State], [Date], row_number() over(order by ID desc) as rn
  from @Audits
)
select C1.State as LatestState,
       C1.Date as LatestDate,
       C2.State as PreviousState,
       C2.Date as PreviousDate
from cte as C1
  cross join cte as C2
where C1.rn = 1 and C2.rn = 2  

You cannot assign a set of aliases to a set of columns like that, because a subquery in the SELECT list is allowed to return no more than one row and one column. Still, it is possible to assign a list of aliases like what you want to a subquery that is used as a derived table, i.e. when it is in the FROM clause (on either side of the join too, if any). Generally it looks like this:

…
FROM
  (subquery) table_alias (column_alias, column_alias, …)
…

So your solution might be something like this:

SELECT
  latest.[LatestState],
  latest.[LatestDate],
  previous.[PreviousState],
  previous.[PreviousDate]
FROM
  (
    SELECT [State], [Date]
    FROM Audits
    WHERE condition to find the highest ID
  ) AS latest ([LatestState], [LatestDate]),  /* comma join = CROSS JOIN */
  (
    SELECT [State], [Date]
    FROM Audits
    WHERE condition to find the next highest ID
  ) AS previous ([PreviousState], [PreviousDate])

Assuming every subselect returns exactly one row, this should pretty much give you what you are looking for.

If you are on SQL Server 2005 or later, though, you could try a different approach:

WITH ranked AS (
  SELECT
    State,
    Date,
    rn = ROW_NUMBER() OVER (ORDER BY ID DESC)
  FROM Audits
)
SELECT
  LatestState   = MAX(CASE rn WHEN 1 THEN State END),
  LatestDate    = MAX(CASE rn WHEN 1 THEN Date  END),
  PreviousState = MAX(CASE rn WHEN 2 THEN State END),
  PreviousDate  = MAX(CASE rn WHEN 2 THEN Date  END)
FROM ranked
WHERE rn BETWEEN 1 AND 2

Basically, this query uses conditional aggregating to display the highest ranked State & Date as the Latest values, and the second-highest ranking State & Date as the Previous values.

Useful reading:

• WITH common_table_expression (Transact-SQL)

• Using Common Table Expressions

• Ranking Functions (Transact-SQL)

• ROW_NUMBER (Transact-SQL)