Select last record created by user, by workstation-CodePudding

I have tried using the search function for this, but none of the results give me anything I can use (although it may be user error - I'm fairly new to this).

What I'm trying to do is find the last record created per user per workstation. User and workstation are recorded in the table I'm selecting from. This is what I've got so far, and it selects everything between these date times. I want to narrow it down to just the last record for each "workstation", "Branch" and "inits".

    select workstation, 
           branch, 
           Inits, 
           document, 
           DateTime 
    from  iheads (nolock) 
    where Inits     = 'AAADB' 
    and  [datetime] >='2021-11-01 23:53:00.000' 
    AND  [datetime] <= '2021-11-30 23:58:00.000' 
    order by Workstation desc

I've included the document as a check, when this is working I'll remove it as it's not needed. Similarly, the Where is also just to narrow down my search results and to check the data - I'll be using a report program to fill in the where and date fields when live.

I've tried using "Top 1", but that only brings back one row, when there should be four (user has been on four different workstations in this selection criteria). Tried using Group By, but I keep getting the aggregate error.

Any help would be appreciated!

CodePudding user response：

The weird thing about SQL tables? They are unordered sets of rows. So, when you say "last record" you actually mean "record with largest value of datetime column.

Let us find those largest values for each combination

                  SELECT MAX([DateTime]) DateTime,
                         workstation, branch, Inits
                    FROM iheads
                   WHERE /* whatever filter criteria you require */
                   GROUP BY workstation, branch, Inits
                   ORDER BY workstation DESC

If that gets the result you want, great.

But if you need other columns from the rows, let's use the query as a subquery. W'll join it to the detail table to find the rows with matching data

SELECT a.workstation, 
       a.branch, 
       a.Inits, 
       a.document, 
       a.[DateTime]
  FROM iheads a
  JOIN (  /* the subquery */
                  SELECT MAX([DateTime]) DateTime,
                         workstation, branch, Inits
                    FROM iheads
                   WHERE /* whatever filter criteria you require */
                   GROUP BY workstation, branch, Inits
       ) b   ON a.workstation=b.workstation
            AND a.branch=b.branch
            AND a.Inits=b.Inits
            AHD a.[DateTime]=b.[DateTime]
 ORDER BY workstation DESC

Pro tip: Avoid mixed case in table and column names. You don't want to wonder whether your RDBMS is case-sensitive, or not.

Pro tip: Avoid reserved words (datetime, for example) in column names.

Pro tip: You're new to this. Stuff like (nolock) is for experts to use to solve performance and deadlocking problems. Don't use that kind of stuff without specific instructions from an expert who has examined your query's execution plan.

By the way, this is sql-server, not mysql.

CodePudding user response：

You can use an analytic function, e.g. MAX OVER, for this.

select *
from
(
  select
    ih.*,
    max(datetime) over (partition by workstation) as max_datetime_for_the_workstation
  from  iheads ih
  where ...
) with_max_datetime
where datetime = max_datetime_for_the_workstation
order by workstation;

As you have mentioned TOP 1in your request, here is how to do this with TOP: You want the top row per workstation, so you must rank the rows per workstation and pick the rows ranked #1. In order to retrieve all #1 rows, use TOP WITH TIES:

select top (1) with ties *
from iheads
where ...
order by rank() over (partition by workstation order by datetime desc);

CodePudding user response：

You can get all the row_number=1 via a top 1 with ties

select top 1 with ties
  workstation,
  branch,
  Inits,
  document,
  [DateTime]
from iheads
where Inits = 'AAADB' 
  and [Datetime] >= cast('2021-11-01 23:53' as datetime)
  and [Datetime] <= cast('2021-11-30 23:58' as datetime)
order by row_number() over (partition by workstation, branch, Inits
                            order by [Datetime] desc)