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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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
June 25, 2022 at 8:30 am
Thank Ed. It works brilliant too and I was trying to work it out with case/where statements to tell you the truth but no success from me of course.
However, can you expand on it? Why MinRN and MaxRN?
June 26, 2022 at 3:58 pm
However, can you expand on it? Why MinRN and MaxRN?
MinRN is the alias given to the rownumber ordered by vacation hours ascending. The lowest (or joint lowest) will get a 1.
MaxRN is the alias for the rownumber ordered by vacation hours descending so the highest will get a 1.
If, for example, the lowest is zero and you have multiple employees with zero hours remaining then this will arbitrarily choose one and not necessarily the same one every time you run the query. When using a rownumber I always include other columns to make the output is deterministic, a good candidate would be a unique employeeID column. It isn’t more accurate to select one employee over another, I just like code to always return the same results. If you wanted to include all the people with the lowest and highest you could replace the row_number with a rank() or dense_rank(). These functions would give all the people with the lowest hours a MinRN of 1. I like dense rank as it doesn’t leave gaps if a rank is repeated. (111123445 vs 111156779).
The case statement in the output uses the value of one of the rownumber columns to work out if the row is the highest and gives it a “highest” label. As we are only including highest and lowest, the else means “lowest”. It would also work with “when MinRN = 1 then ‘Min’.
In order to use the rownumber columns in the where filter, the query has to be nested one level. You could do this with a cte, but I prefer to use derived tables. This is personal preference, not for any performance reasons.
June 27, 2022 at 8:09 am
Ok that's great many thanks for giving such a good explanation step-by-step. So very helpful 🙂
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply