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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply