Getting all fields from table filtered by MAX(Column1)

Question

I have table with some data, for example

ID Specified TIN Value
----------------------
1       0   tin1   45
2       1   tin1   34
3       0   tin2   23
4       3   tin2   47
5       3   tin2   12

I need to get rows with all fields by MAX(Specified) column. And if I have few row with MAX column (in example ID 4 and 5) i must take last one (with ID 5) finally the result must be

ID Specified TIN Value
-----------------------    
2       1   tin1   34
5       3   tin2   12

if you are taking last id (5) then why does your result contains record with ID=2?? — Utsav, Oct 17 '15 at 10:14
Sorry, i didn't write additional information. I need to get rows with all fields by MAX(Specified) column groupped by TIN column — Nursultan Aidarkulov, Oct 17 '15 at 10:20

Utsav · Answer 1 · 2015-10-17T11:12:14.277

0

Edit: Updated query after question edit. Here is the fiddle

http://sqlfiddle.com/#!9/20e1b/1/0

 SELECT * FROM TBL WHERE ID IN (
 SELECT max(id)  FROM 
 TBL WHERE SPECIFIED IN 
    (SELECT MAX(SPECIFIED) FROM TBL
     GROUP BY TIN)
 group by specified)

I am sure we can simplify it further, but this will work.

select * from tbl where id =(
SELECT MAX(ID) FROM 
tbl where specified =(SELECT MAX(SPECIFIED) FROM tbl))

edited Oct 17 '15 at 11:12

answered Oct 17 '15 at 10:15

Utsav

7,914
2
17
38

I edited the question with aditional information. Sorry, your code is not working after edited. I need to get rows with all fields by MAX(Specified) column groupped by TIN column – Nursultan Aidarkulov Oct 17 '15 at 10:22
check now. If it works then please accept the answer by clicking the tick symbol on left of the question. – Utsav Oct 17 '15 at 10:39
No - this code taking only n rows if we have n rows with n different Specified values – Nursultan Aidarkulov Oct 17 '15 at 11:06
What are you talking about. Check this fiddle http://sqlfiddle.com/#!9/20e1b/1/0 and tell me what is the issue. It is returning the result as you wanted. – Utsav Oct 17 '15 at 11:12
Please check this http://sqlfiddle.com/#!9/d474eb/1. I added tin3 and tin4 values. But the code returning only tin3 and tin4 - where are tin2 and tin1 ?? – Nursultan Aidarkulov Oct 17 '15 at 11:16
Ok I see it. This is because in the new data, the max(specified) for tin1 and tin2 is same for tin3 and tin4. Let me see if I can correct it. In the meantime you go though this http://stackoverflow.com/questions/1895110/row-number-in-mysql and see if you can make any progress – Utsav Oct 17 '15 at 11:29

Giorgi Nakeuri · Accepted Answer · 2015-10-17T11:23:36.827

0

This will give the desired result with using window function:

;with cte as(select *, row_number(partition by tin order by specified desc, id desc) as rn
             from tablename)
select * from cte where rn = 1

edited Oct 17 '15 at 11:23

answered Oct 17 '15 at 10:24

Giorgi Nakeuri

35,155
8
47
75

Good code. But if I have row with ID=6 Specified=0 TIN=tin3 Value=18 - this row should be involved in finally output! – Nursultan Aidarkulov Oct 17 '15 at 10:38
Thank you very much!! The code works correctly! To accept answer i have little reputation, but for the future i will save your name))). You really helped me – Nursultan Aidarkulov Oct 17 '15 at 11:38

Gordon Linoff · Answer 3 · 2015-10-17T20:36:26.867

One method is to use window functions, row_number():

select t.*
from (select t.*, row_number() over (partition by tim
                                     order by specified desc, id desc
                                    ) as seqnum
      from t
     ) t
where seqnum = 1;

However, if you have an index on tin, specified id and on id, the most efficient method is:

select t.*
from t
where t.id = (select top 1 t2.id
              from t t2
              where t2.tin = t.tin 
              order by t2.specified desc, id desc
             );

The reason this is better is that the index will be used for the subquery. Then the index will be used for the outer query as well. This is highly efficient. Although the index will be used for the window functions; the resulting execution plan probably requires scanning the entire table.

@NursultanAidarkulov . . . I think I misunderstood the question (and I'm not the only one). You seem to want your conditions per `tin`. — Gordon Linoff, Oct 17 '15 at 20:36

Getting all fields from table filtered by MAX(Column1)

3 Answers3