max with distinct in sql-CodePudding

I have a table with rows/data in the following structure

I want a query that results in the red line(max credit no)

I have tried these:

SELECT   employee no, MAX(credit no)
FROM     mytable
GROUP BY employee no

SELECT  distinct employee no, MAX(credit no)
FROM     mytable
GROUP BY employee no

but it doesn't give me the red line records

CodePudding user response：

You can use correlated subquery :

select mt.*
from mytable mt
where mt.creditno = (select max(mt1.creditno) 
                     from mytable mt1 
                     where mt1.employeeno = mt.employeeno
                    );

CodePudding user response：

You sould use group by and order by clouse. if you want to just top 2 employee_no, write to select top 2

select employee_no, max(credit_no) as max_credit_no from yourtable group by employee_no order by max_credit_no desc

select top 2, employee_no, max(credit_no) as max_credit_no from yourtable group by employee_no order by max_credit_no desc

CodePudding user response：

if I understand correctly, I would use the line number to sort the values in descending order then select the lines.

 CREATE TABLE #data
( 
   employee_no int,
   credit_no int,
   stat nvarchar(3)
) 

INSERT INTO #data
  (employee_no, credit_no, stat)
VALUES
  (100022, 244, 'ret'),
  (100022, 245, 'emp'),
  (100023, 244, 'vac'),
  (100023, 245, 'ret'),
  (100023, 246, 'emp')*/

And select:

select * 
  from (
    select employee_no, 
           credit_no, 
           stat, 
           ROW_NUMBER() over (partition by employee_no order by credit_no desc) id
    from #data
  )x
  where id = 1

And result: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=2b97f6e553231a6a9524b143e730f323