SQL Max Value only

Question

In the following script I would like the BH_StartDate column to only return the highest date value as it presently returns all date values for all users. Is it possible to have only the Max date value with this script?

 SELECT         DISTINCT [dbo].[BH_Historic_Report_TaskAudit].BH_UserGuid       AS  'BighandUserGUID',
                BH_UserRole                                                     AS  'BighandUserRole',
                [dbo].[BH_Users].BH_FirstName                                   AS  'LastName',
                [dbo].[BH_Users].BH_Lastname                                    AS  'FirstName',
                [dbo].[BH_Users].BH_UserName                                    AS  'DisplayName',
                BH_StartDate                                                    AS  'StartDate', 
                [dbo].[BH_Users].BH_Description                                 AS  'JobDescription'

FROM            [dbo].[BH_Historic_Report_TaskAudit]
JOIN            [dbo].[BH_Users]
ON              [dbo].[BH_Historic_Report_TaskAudit].BH_UserGuid = [dbo].[BH_Users].BH_UserGuid
WHERE           BH_StartDate < '2015-04-29 00:00:00.000' AND BH_UserRole = 0 OR BH_UserRole = 2
ORDER BY        [dbo].[BH_Users].BH_LastName

Do you want the max date for each user or just 1 row that contains the max date from the entire result set? — Mark He
– Mark He, Commented Nov 3, 2015 at 15:21
Hello Harry welcome to StackOverflow, next time try to provide a SqlFiddle with a schema and some data so we can understand the problem better and give you an answer much faster – Also please read How to ask And How to create a Minimal, Complete, and Verifiable example. Right now isnt clear what is your desire result — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 3, 2015 at 15:38
Apologies for not being clear Juan. The desired result would display the max date for each user. Adding BH_UserRole IN ( 0, 2 ) as you have suggested has simplified the query and works well with Gadaju's script below. Thankyou kindly for your assistance. — Harry
– Harry, Commented Nov 4, 2015 at 8:43

gadaju · Accepted Answer · 2015-11-03 16:06:16Z

1

I think you might have first name and last name mixed in the column aliases. Also when you use both AND and OR in the where clause, you should parenthesis so as to avoid ambiguity about where the major conjuncts/disjuncts are.

My idea is to add a calculated column to BH_Historic_Report_TaskAudit to show the max BH_StartDate per user. It's fine to do the date filter at this point. Then we join and filter for the roles we want and require that the BH_StartDate = max_BH_StartDate

SELECT DISTINCT 
  hrt.BH_UserGuid AS 'BighandUserGUID',
  hrt.BH_UserRole  AS  'BighandUserRole',
  bu.BH_FirstName  AS 'LastName',
  bu.BH_Lastname  AS 'FirstName',
  bu.BH_UserName AS 'DisplayName',
  hrt.BH_StartDate AS 'StartDate', 
  bu.BH_Description AS 'JobDescription'
FROM (
      select 
        BH_UserGuid, BH_UserRole, BH_StartDate,
        max(BH_StartDate) over (partition by BH_UserGuid) max_BH_StartDate 
      from [dbo].[BH_Historic_Report_TaskAudit] 
      where BH_StartDate < '2015-04-29 00:00:00.000'
      ) hrt
JOIN [dbo].[BH_Users] bu
ON hrt.BH_UserGuid = bu.BH_UserGuid
WHERE 
  (hrt.BH_UserRole = 0 OR hrt.BH_UserRole = 2)
     and
   hrt.BH_StartDate = hrt.max_BH_StartDate
ORDER BY  bu.BH_LastName

edited Nov 3, 2015 at 16:06

answered Nov 3, 2015 at 16:00

gadaju

4163 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harry Over a year ago

Thanks for your help Gadaju this query works perfectly. Your clarity and assistance is greatly appreciated.

Peter · Accepted Answer · 2015-11-03 16:57:32Z

0

You can also create 2 #TempTable one for your all the records i.e. Master Table and create another table just to find the out MAX value from column. Then update the Column IN Master Table.

answered Nov 3, 2015 at 16:57

Peter

7431 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Max Value only

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related