CODEWITHSUNDEEP
sqlsql-servert-sqlunion
chamara
chamara

Reputation: 12709

issue with sql select query

i have the following query 

SELECT DISTINCT dr.Revision 
FROM tblActionHeader ah 
    INNER JOIN tblActionType at ON at.ActionTypeID = ah.ActionTypeID 
    INNER JOIN tblDocumentRevisionActionHeader drah ON drah.ActionHeaderID = ah.ActionHeaderID 
    INNER JOIN tblDocumentRevision dr on dr.DocumentRevisionID = drah.DocumentRevisionID 
    INNER JOIN tblDocumentHeader dh on dh.DocumentHeaderID = dr.DocumentHeaderID 
WHERE at.ActionTypeID=2 
UNION SELECT '(All)' 
      FROM tblActionHeader ah

it returns the following result set

-
(All)
0
0a
1

i need '(All)' to take at the top of the result set how can i do this?

Upvotes: 1

Views: 125

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332731

Two ways - first would be to put the "All" at the top of the UNION:

SELECT '(All)' AS revision
UNION ALL
SELECT DISTINCT dr.Revision 
  FROM tblActionHeader ah 
  JOIN tblActionType at ON at.ActionTypeID = ah.ActionTypeID 
  JOIN tblDocumentRevisionActionHeader drah ON drah.ActionHeaderID = ah.ActionHeaderID   
  JOIN tblDocumentRevision dr ON dr.DocumentRevisionID = drah.DocumentRevisionID 
  JOIN tblDocumentHeader dh ON dh.DocumentHeaderID = dr.DocumentHeaderID 
 WHERE at.ActionTypeID = 2

The second is to assign an arbitrary value for ordering by:

SELECT x.revision
  FROM (SELECT '(All)' AS revision, 1 AS sort_order 
         UNION ALL
        SELECT DISTINCT dr.Revision, 2 AS sort_order 
          FROM tblActionHeader ah 
          JOIN tblActionType at ON at.ActionTypeID = ah.ActionTypeID 
          JOIN tblDocumentRevisionActionHeader drah ON drah.ActionHeaderID = ah.ActionHeaderID   
          JOIN tblDocumentRevision dr ON dr.DocumentRevisionID = drah.DocumentRevisionID 
          JOIN tblDocumentHeader dh ON dh.DocumentHeaderID = dr.DocumentHeaderID 
         WHERE at.ActionTypeID = 2) x
ORDER BY x.sort_order, x.revision

I used the derived table approach in this example to make sure that only the revision column is in the final result set.

Use UNION ALL if you know the values will be distinct/unique -- UNION removes duplicates, and is slower for that reason.

Upvotes: 5

Elias Hossain
Elias Hossain

Reputation: 4469

SELECT Revision
FROM( 
    SELECT DISTINCT dr.Revision 
    FROM tblActionHeader ah 
        INNER JOIN tblActionType at ON at.ActionTypeID = ah.ActionTypeID 
        INNER JOIN tblDocumentRevisionActionHeader drah ON drah.ActionHeaderID = ah.ActionHeaderID 
        INNER JOIN tblDocumentRevision dr on dr.DocumentRevisionID = drah.DocumentRevisionID 
        INNER JOIN tblDocumentHeader dh on dh.DocumentHeaderID = dr.DocumentHeaderID 
    WHERE at.ActionTypeID=2 
    UNION SELECT '(All)' AS Revision
    ) tempT
ORDER BY Revision DESC

Upvotes: 1

Saidur Rahman
Saidur Rahman

Reputation: 422

Try

SELECT '(All)' from tblActionHeader ah
union all
select distinct dr.Revision from tblActionHeader ah inner join tblActionType at 
       on at.ActionTypeID = ah.ActionTypeID 
   inner join tblDocumentRevisionActionHeader drah 
   on drah.ActionHeaderID = ah.ActionHeaderID inner join tblDocumentRevision dr 
   on dr.DocumentRevisionID = drah.DocumentRevisionID inner join tblDocumentHeader dh 
   on dh.DocumentHeaderID = dr.DocumentHeaderID where at.ActionTypeID=2

Upvotes: 3

tmjam
tmjam

Reputation: 1039

Not sure if you looking for this ?

select top 1 as All from 
(
select distinct dr.Revision from tblActionHeader ah inner join tblActionType at 
on at.ActionTypeID = ah.ActionTypeID 
 inner join tblDocumentRevisionActionHeader drah 
 on drah.ActionHeaderID = ah.ActionHeaderID inner join tblDocumentRevision dr 
on dr.DocumentRevisionID = drah.DocumentRevisionID inner join tblDocumentHeader dh 
on dh.DocumentHeaderID = dr.DocumentHeaderID where at.ActionTypeID=2
)

Upvotes: 0

Related Questions