I have a sql query that does a union on three queries.(Using SQL Server 2008) It works pretty good. However, in reality the first query is the one which is most likely to pull back results. Its important that the first query only return 1 row and that the row returned be the most recent
. (There could unfortunately be multiple rows that fit the criteria in the where clause)
Question : How do I make certain the row returned by the first query is the most recent record?
I tried to add "order by dhpMVRRequest.CreateDate desc" to the very bottom of the sql statement. SQL runs the query fine but it still does not bring back the most recent row for the first query. Is there a way I can do this. Any advice or direction would be most appreciated.
SELECT Top 1 CASE WHEN DataValidationDue > GETDATE() THEN 'Pending' ELSE 'Overdue' END AS STATUS, 'DV' AS Action, Convert(varchar,DataValidationDue,111) AS DateDue,ID,''as[Title],CreateDate
WHERE DriverRecordID = @DriverRecordID and Cancelled is null and
(DataValidationDue IS NOT NULL and DataValidationComplete is null)
Select CASE WHEN DueDate > GETDATE() THEN 'Incomplete' ELSE 'Overdue' END as [STATUS], b.URL AS Action, Convert(varchar,DueDate,111) AS DateDue, a.courserecordid as [ID], b.Title,a.CreateDate
From lms_assignments a join LMS_Courses b on b.ID = a.CourseRecordID
where a.DriverRecordID = @DriverRecordID and a.DeleteDate is null and b.Active = 1
SELECT 'Optional' AS STATUS, 'OLU' AS Action, Convert(varchar,GETDATE()+90,111) AS DateDue,ID, '' AS Title,CreateDate
Where id=@DriverRecordID and driverlicensestate='GA'
//I tried adding this to the bottom here...it made no difference
order by dhpMVRRequest.CreateDate desc