June 23, 2022 at 1:54 pm
Hi Phil Parkin. I've just realised that, even that your query below works in a sense, it doesn't give me 2 separated variables with the names of 'min hours' and 'max hours' vacationhours. At the bottom of your query I have put what I tried which is quite close but only gives me the max value.
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
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
This is what i tried. It gives me 2 records for min and max but gives me only the max even if i change the order by with desc or asc;
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 ASC
June 23, 2022 at 1:58 pm
Sorry but to note that in your query, each variable (max and min) has a record name but only brings the first one ("'Max_VacationHours'") as a header. The second you put, "Min_VacationHours", but doesn't bring it.
June 23, 2022 at 2:05 pm
You are using terminology which is confusing me.
There are no variables in my code. Zero.
Nor is there a 'header' called Max_VacationHours.
The second you put, "Min_VacationHours", but doesn't bring it.
No I did not. I don't get what you are talking about.
I think you need to provide the layout you want to see. A screenshot is probably good enough.
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 23, 2022 at 2:17 pm
This query:
Select TOP (1) 'Max Vacation Hours' as 'Vacation Hours', *
From [HumanResources].[Employee]
UNION ALL
could give you any row in the table since it doesn't have an ORDER BY.
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!
June 23, 2022 at 2:17 pm
Are you saying that this query didn't work?:
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!
June 23, 2022 at 2:25 pm
Apologies for my poor explanation. I just mixed terms like variables (var) which is misleading.
It works Phil but it doesn't give the name on each records. I would need two records with max and min vacationhours and each row to say Max_VacationHours and Min_VacationHours if it makes any sense.
Hope it helps. Thank you
June 23, 2022 at 2:34 pm
The first 2 fields would be as below (first row below are the headings);
Vacation_Hours ---Hours
Max_Hours---------99
Min_Hours----------0
June 23, 2022 at 2:50 pm
WITH mx
AS (SELECT TOP (1)
VacationHours = 'Max_Hours'
,*
FROM HumanResources.Employee
ORDER BY vacationhours DESC)
,mn
AS (SELECT TOP (1)
VacationHours = 'Min_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 23, 2022 at 3:33 pm
Thank Phil. It gives me the below errors;
Msg 209, Level 16, State 1, Line 35
Ambiguous column name 'vacationhours'.
Msg 8156, Level 16, State 1, Line 30
The column 'VacationHours' was specified multiple times for 'mx'.
Msg 209, Level 16, State 1, Line 41
Ambiguous column name 'vacationhours'.
Msg 8156, Level 16, State 1, Line 36
The column 'VacationHours' was specified multiple times for 'mn'.
June 23, 2022 at 3:39 pm
OK, too many columns called vacation hours.
WITH mx
AS (SELECT TOP (1)
VacHours = 'Max_Hours'
,*
FROM HumanResources.Employee
ORDER BY vacationhours DESC)
,mn
AS (SELECT TOP (1)
VacHours = 'Min_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 23, 2022 at 3:40 pm
Perfect 🙂
So thankful it works perfect. Now have to try to go through it and actually understand it. That was brilliant.
June 23, 2022 at 8:38 pm
SELECT *,
CASE WHEN MaxRN =1 THEN 'Max' ELSE 'Min' END AS MaxMinDesc
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY VacationHours DESC) AS MaxRN,
ROW_NUMBER() OVER (ORDER BY VacationHours ASC) AS MinRN
FROM HumanResources.Employee
) AS a
WHERE MaxRN = 1 OR MinRN = 1
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply