CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
htm11h
htm11h

Reputation: 1779

Combining the results of two queries with identical columns into a single result set

I could use some guru help returning this data in one call....

SELECT TOP 10 tblData.*, (
    SELECT  TOP 10 tblData.*
    FROM    tblData
    WHERE   pk >= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND
                        CAST(event_date_time as DATE) = '10/18/2013'
    ORDER BY pk ASC
    )
FROM    tblData
WHERE   pk <= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND 
        CAST(event_date_time as DATE) = '10/18/2013'
ORDER BY pk DESC

Upvotes: 0

Views: 52

Answers (2)

htm11h
htm11h

Reputation: 1779

I finally produced a working solution, posting here for anyone else that may come across this....

Select * From
(SELECT TOP 10 tblData.*
FROM    tblData
WHERE   pk <= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND
    CAST(event_date_time as DATE) = '10/18/2013'
UNION
SELECT  TOP 10 tblData.*
FROM    tblData
WHERE   pk >= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND
    CAST(event_date_time as DATE) = '10/18/2013') A
ORDER BY pk ASC

Upvotes: 0

Jompper
Jompper

Reputation: 1402

You should try using UNION or UNION ALL

UNION, Specifies that multiple result sets are to be combined and returned as a single result set.

ALL, Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

(SELECT TOP 10 tblData.*
FROM    tblData
WHERE   pk <= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND 
        CAST(event_date_time as DATE) = '10/18/2013'
ORDER BY pk DESC)
UNION 
(SELECT  TOP 10 tblData.*
FROM    tblData
WHERE   pk >= 5481 AND dev_ID = 'REC1' AND code_ID = 'FMU' AND
        CAST(event_date_time as DATE) = '10/18/2013'
ORDER BY pk ASC)

Upvotes: 1

Related Questions