Bringing all the columns not just the newly created

Question

Bringing all the columns not just the newly created

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

jutunon SSC Enthusiast Points: 134 More actions · Answer 1

Just one more question. If I want for only to appear the fields 'MaxMinDesc' and the 'VacationHours' how would I do it? I have tried taking the asterixes off as below and it brings the fields 'MaxMinDesc' but not the 'VacationHours'

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

Ed B Hall of Fame Points: 3674 More actions · Answer 2

jutunon wrote:

Just one more question. If I want for only to appear the fields 'MaxMinDesc' and the 'VacationHours' how would I do it? I have tried taking the asterixes off as below and it brings the fields 'MaxMinDesc' but not the 'VacationHours'

I don't know all the columns in the table so I retain the initial SELECT *, but the outer select needs only the MinMaxDesc and theVacationHours.

SELECT CASE WHEN a.MaxRN =1 THEN 'Max' ELSE 'Min' END AS MaxMinDesc,
       a.VacationHours
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

I assumed you needed other columns like the Name or EmployeeID in the output which is why I suggested using row_number. If you only need the VacationHours then there are probably more efficient ways to do it. row_number is useful because it allows you to get a max or min and keep the entire row.

SELECT 'Min' AS MinMaxDesc,
        MIN(VacationHours) AS VacationHours
FROM   HumanResources.Employee
UNION  ALL
SELECT 'Max',
        MAX(VacationHours) AS VacationHours
FROM   HumanResources.Employee

This reply was modified 2 years, 7 months ago by Ed B.

jutunon SSC Enthusiast Points: 134 More actions · Answer 3

That's great many thanks. Gosh wish I had similar level. Much appreciated for the thorough explanations.