MAX syntax

[Imakeoil]

I am struggling to find the correct syntax for the max function. I want it to return the eqmt, endtime and shiftindex, but only the row with the highest shiftindex for each combination.
So if I have
eqmt endtime shiftindex
1 2 1
1 7 2
I want it to return
1 7 2


Here is the select statement

select
(tbl1.eqmt)--, tbl2.unit, tbl2.eqmttype
-- , max(tbl1.shiftindex)
,tbl1.endtime--, tbl1.shiftindex
--into #pmdowntime
from tbl1 tbl1, tbl2 tbl2
where tbl1.shiftindex = tbl2.shiftindex
and tbl1.eqmt = tbl2.eqmtid
--and (tbl1.shiftindex between @startd and @endd)
--and tbl1.shiftindex = (SELECT max (shiftindex)as sh,eqmt from tbl1
--WHERE status = 1 and reason = 3501
--group by eqmt)
--and (tbl1.status = 1) and (tbl1.reason = 3501)
group by tbl1.eqmt, tbl2.unit, tbl2.eqmttype,
tbl1.shiftindex,
tbl1.endtime
order by tbl1.eqmt asc

Neither of the methods I have tried have given me the answer I am looking for.
Thanks

 

[markros]

SQL Server 2005 and up

select * from (select eQmt, EndTime, ShiftIndex, row_number() over (partition by eQmt, EndTime ORDER by ShiftIndex DESC) as RowNum from myTable) X where RowNum = 1

See also
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

PluralSight Learning Library

 

[Imakeoil]

Thanks markros, but I am using SQL 2000 and rownumber () is not a supported function. I have also tried using the exists function in the where clause, a select clause in the join and numbering the rows to use top, but neither have produced the desired result.

 

[Axworthy]

Try this:

select top 1 eqmt, endtime, shiftindex 
    from tbl1
    where shiftindex = (select max(shiftindex) from tbl1)

 

[markros]

Did you read the blogs I pointed you to? The first two listed approaches and the packed value approach work in SQL Server 2000. Do you want me to write down the solution for you or you'll try to work it out from these blogs?

PluralSight Learning Library

 

[Imakeoil]

Hi markros, I did read the blogs but could not make any of the examples work for me. I think perhaps it has to do with the fact that, though I use SQL everyday and have a very good basic understanding I really don't understand how it works. I have used the simple select max(shiftindex), eqmt statement and put the results into a temp table then joined that table with the other. This has given me the desired results. I only wish I had thought to try it sooner . Thanks for all your help.

 

[markros]

select T.* myTable T where T.ID = (select top 1 ID from MyTable T1 where T1.eQmt = T.eQmt and T1.EndTime = T.EndTime ORDER by ShiftIndex DESC)

This is one of the possible approaches.

PluralSight Learning Library