• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

SQL Union Query Question With Order By

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
             
FROM dhpMVRRequest 

WHERE DriverRecordID = @DriverRecordID and Cancelled is null and 
(DataValidationDue IS NOT NULL and DataValidationComplete is null) 
			
Union
			
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
			
Union
 
SELECT  'Optional' AS STATUS, 'OLU' AS Action, Convert(varchar,GETDATE()+90,111) AS DateDue,ID, '' AS Title,CreateDate
			
From dhpDriver

Where id=@DriverRecordID and driverlicensestate='GA'

//I tried adding this to the bottom here...it made no difference
order by dhpMVRRequest.CreateDate desc

Open in new window

0
jazzcatone
Asked:
jazzcatone
1 Solution
 
Paulmc999Commented:
Could you add a column datestamp and insert time of insertion, then always return the most recent timestamped row?
0
 
Patrick MatthewsCommented:
If you want the "pieces" of a union query to sort together, use a dummy field:


SELECT 1 AS MyOrder, a, b, c
FROM table1

UNION ALL

SELECT 2 AS MyOrder, a, b, c
FROM table2

UNION ALL

SELECT 3 AS MyOrder, a, b, c
FROM table3
ORDER BY MyOrder, a

Open in new window

0
 
jazzcatoneAuthor Commented:
I have that already with CreateDate in the DhpMvrRequest table . The question is essentially how to use it. Essentially I want to

1) Keep the Union

2) Make certain the top most recent row is returned in the first query.  

Should mention I also tried to add an order by directly after the first query but with the unions sql server doesn't like it and barks.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Paulmc999Commented:
Can I see the table structures?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would reading this article help?
http://www.experts-exchange.com/A_3203.html
0
 
jazzcatoneAuthor Commented:
Let me put this another way.  If I took the top query by itself and added "order by CreateDate desc" . (Would look like whats below)

 I would get what I want just fine for the first query .

 Question is what do I need to do to keep this, plus keep the other unions.


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
             
FROM dhpMVRRequest 

WHERE DriverRecordID = @DriverRecordID and Cancelled is null and 
(DataValidationDue IS NOT NULL and DataValidationComplete is null) 

order by CreateDate desc

Open in new window

0
 
jvejskrabCommented:

Try to rewrite the query like this:


SELECT CASE WHEN DataValidationDue > GETDATE() THEN 'Pending' ELSE 'Overdue' END AS STATUS, 'DV' AS Action,  Convert(varchar,DataValidationDue,111) AS DateDue,ID,''as[Title],CreateDate
             
ROM dhpMVRRequest

WHERE dhpMVRRequest.ID = (SELECT MAX(ID)     /* replace ID by right primary key name of tabe dhpMVRRequest */
                                                FROM dhpMVRRequest
                                                WHERE DriverRecordID = @DriverRecordID
                                                            AND Cancelled is null
                                                            AND (DataValidationDue IS NOT NULL AND DataValidationComplete is null) )

Union

Then you can also omit the TOP 1 clause

0
 
jazzcatoneAuthor Commented:
Excellent thinking outside the box. Thanks so much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now