Bringing all the columns not just the newly created

  • Hi. I have the below query but only brings the 'Max_VacationHours'  column. As yo may guess I am quite new to SQL.

    How could I do so that all the table columns come up not that the newly created query?

    Select MAX (vacationhours) as 'MinMax_VacationHours'

    From [HumanResources].[Employee]

    Union

    Select MIN (vacationhours)

    From [HumanResources].[Employee]

  • Perhaps

    SELECT MAX (vacationhours) as 'Max_VacationHours', MIN (vacationhours) as 'Min_VacationHours'

    From [HumanResources].[Employee]

  • Maybe this?

    Select TOP (1) 'Max Vacation Hours', * 
    From [HumanResources].[Employee]
    ORDER BY vacationhours DESC

    Union

    Select TOP (1) 'Min Vacation Hours', *
    From [HumanResources].[Employee]
    ORDER BY vacationhours

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks SS Guru but it gives me the below error;

    ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

  • Sorry Guru. I got it wrong.

    The error it brings is "Incorrect syntax near the keyword 'Union'.

  • Ok, I think that I got it sorted.

    All I've done is to omit ORDER BY function in the first query as below;

    Select TOP (1) 'Max Vacation Hours', *

    From [HumanResources].[Employee]

    Union

    Select TOP (1) 'Min Vacation Hours', *

    From [HumanResources].[Employee]

    ORDER BY vacationhours

  • That will give you the same result twice. There is another solution, hopefully someone else will provide it as I am going to be busy for a while. By the way,  use UNION ALL rather than UNION in these types of queries.

    UNION on its own adds an implicit DISTINCT to the results it returns.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Ok, point taken about the UNION ALL. Many thanks

    Always good to learn alternative ways to learn if anybody can provide a solution to it to avoid giving the same result twice

  • Or maybe a combination of the proposed queries? Something like

    WITH LowestHighest AS (

    SELECT MAX (vacationhours) as 'Max_VacationHours', MIN (vacationhours) as 'Min_VacationHours'

    From [HumanResources].[Employee]

    )

    SELECT 'Lowest' AS VacationHoursLeft, e.*, lh.Min_VacationHours AS VacationHours

    FROM [HumanResources].[Employee] AS e

    JOIN LowestHighest AS lh ON e.vacationhours = lh.Min_VacationHours

    UNION ALL

    SELECT 'Highest' AS VacationHoursLeft, e.*, lh.Max_VacationHours AS VacationHours

    FROM [HumanResources].[Employee] AS e

    JOIN LowestHighest AS lh ON  e.vacationhours = lh.Max_VacationHours

    ORDER BY VacationHours

    (not tested in any way, shape or form)

  • Another version.

    WITH mx
    AS (SELECT TOP (1)
    Dsc = 'Max Vacation Hours'
    ,*
    FROM HumanResources.Employee
    ORDER BY vacationhours DESC)
    ,mn
    AS (SELECT TOP (1)
    Dsc = 'Min Vacation Hours'
    ,*
    FROM HumanResources.Employee
    ORDER BY vacationhours)
    SELECT *
    FROM mx
    UNION ALL
    SELECT *
    FROM mn;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin, on saying that I've just realised that it doesn't give correct results.

    Select TOP (1) 'Max Vacation Hours' as 'Vacation Hours', *

    From [HumanResources].[Employee]

    UNION ALL ---- UNION on its own adds an implicit DISTINCT to the results it returns.

    Select TOP (1) 'Min Vacation Hours', *

    From [HumanResources].[Employee]

    ORDER BY vacationhours

    If I put the original one that you gave me as below (in the up query I omitted the first ORDER BY function ) it gives me the error "The error it brings is "Incorrect syntax near the keyword 'Union';

    Select TOP (1) 'Max Vacation Hours', *

    From [HumanResources].[Employee]

    Union

    Select TOP (1) 'Min Vacation Hours', *

    From [HumanResources].[Employee]

    ORDER BY vacationhours

  • Phil, I've tried the below and brings error "Incorrect syntax near 'des'.";

    WITH mx

    AS (SELECT TOP (1)

    Dsc = 'Max Vacation Hours'

    ,*

    FROM HumanResources.Employee

    ORDER BY vacationhours DESC)

    ,mn

    AS (SELECT TOP (1)

    Dsc = 'Min Vacation Hours'

    ,*

    FROM HumanResources.Employee

    ORDER BY vacationhours)

    SELECT *

    FROM mx

    UNION ALL

    SELECT *

    FROM mn;

  • Ok, this actually works I must have run the wrong query 🙂

    But, why "mx" and "mn" and what does the WITH function dos in this case?

    I suppose it's max and min but I had never used alias like this if you can explain in your own words please for me to understand it

     

    WITH mx

    AS (SELECT TOP (1)

    Dsc = 'Max Vacation Hours'

    ,*

    FROM HumanResources.Employee

    ORDER BY vacationhours DESC)

    ,mn

    AS (SELECT TOP (1)

    Dsc = 'Min Vacation Hours'

    ,*

    FROM HumanResources.Employee

    ORDER BY vacationhours)

    SELECT *

    FROM mx

    UNION ALL

    SELECT *

    FROM mn;

  • SELECT *
    FROM (
    SELECT TOP (1) vacationhours AS 'Max_VacationHours', *
    FROM [HumanResources].[Employee]
    ORDER BY vacationhours DESC
    ) AS q1

    UNION ALL

    SELECT *
    FROM (
    SELECT TOP (1) vacationhours AS 'Min_VacationHours', *
    FROM [HumanResources].[Employee]
    ORDER BY vacationhours
    ) AS q2

    ORDER BY 1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • jutunon wrote:

    Ok, this actually works I must have run the wrong query 🙂

    But, why "mx" and "mn" and what does the WITH function dos in this case?

    I suppose it's max and min but I had never used alias like this if you can explain in your own words please for me to understand it

    mx and mn are aliases for the two 'virtual tables' which are created by running the queries they reference.

    The WITH statement performs the definition of one or more of these 'virtual tables', the results of which are referenced in the subsequent SELECT ... UNION ALL ... expression.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 33 total)

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