Bringing all the columns not just the newly created

  • 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

  • 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.

  • 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.

  • 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!

  • 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!

  • 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

  • The first 2 fields would be as below (first row below are the headings);

    Vacation_Hours ---Hours

    Max_Hours---------99

    Min_Hours----------0

     

  • 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.

  • 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'.

  • 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.

  • Perfect 🙂

    So thankful it works perfect. Now have to try to go through it and actually understand it. That was brilliant.

  • 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
  • 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?

  • jutunon wrote:

    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.

  • 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