June 21, 2022 at 10:02 am
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]
June 21, 2022 at 10:22 am
Perhaps
SELECT MAX (vacationhours) as 'Max_VacationHours', MIN (vacationhours) as 'Min_VacationHours'
From [HumanResources].[Employee]
June 21, 2022 at 10:50 am
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.
June 21, 2022 at 11:30 am
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.
June 21, 2022 at 11:35 am
Sorry Guru. I got it wrong.
The error it brings is "Incorrect syntax near the keyword 'Union'.
June 21, 2022 at 11:39 am
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
June 21, 2022 at 11:44 am
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.
June 21, 2022 at 11:48 am
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
June 21, 2022 at 12:28 pm
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)
June 21, 2022 at 1:58 pm
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.
June 21, 2022 at 2:05 pm
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
June 21, 2022 at 2:07 pm
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;
June 21, 2022 at 2:11 pm
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;
June 21, 2022 at 2:35 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
June 21, 2022 at 3:16 pm
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 32 total)
You must be logged in to reply to this topic. Login to reply