Post reply

ORDER BY using UNION

  • March 13, 2015 at 2:11 pm

    #295951

    Hey guys -

    My task seemed simple: Select 'dates' from two different tables and create a single, ordered set of results. So I've been working with this:

    SELECT [ClosedDate] FROM [ClosedDates] AS UnavailableDate WHERE [Deleted] = 0 AND [ClosedDate] >= GetDate()

    UNION

    SELECT [ReservationDate] AS UnavailableDate FROM [Reservations] WHERE [ReservationTypeID] > 2 AND [ReservationDate] >= GetDate()

    --ORDER BY UnavailableDates

    1) Even though I have given both date fields the same Alias, it still comes out in the results as 'ClosedDate'. Why is that?

    2) When I attempt to use ORDER BY, SQL Server barks at me saying that it is an invalid column name even though most resources I find online seem to think this is totally valid.

    Any thoughts?

    Thanks a million!

  • March 13, 2015 at 2:15 pm

    #1783990

    Well no you haven't aliased your columns the same(you aliased your table) so SQL Server is using the column name of the first select which is why the order by is complaining at you.

  • March 13, 2015 at 2:31 pm

    #1783992

    You need the alias "Unavailabledate" next to your column name in the first query:

    with CLOSEDDATES AS (SELECT '2016-01-01 10:00' CLOSEDDATE)

    ,

    RESERVATIONS AS (SELECT '2016-01-02 10:00' RESERVATIONDATE)

    --SELECT [ClosedDate] FROM [ClosedDates] AS UnavailableDate WHERE [ClosedDate] >= GetDate()

    --SHOULD BE:

    SELECT [ClosedDate] AS UnavailableDate FROM [ClosedDates] WHERE [ClosedDate] >= GetDate()

    UNION

    SELECT [ReservationDate] AS UnavailableDate FROM [Reservations] WHERE [ReservationDate] >= GetDate()

    ORDER BY UnavailableDate

  • March 13, 2015 at 2:36 pm

    #1783994

    [facepalm]

    D'oh! It's Friday and I'm tired. Thanks for pointing out the stupid mistake....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply