SQL : Getting max ID row within a substring

Question

This is my first call for help, most of the time I have been able to figure out from google searches into StackOverflow but this time I can't seem to find an answer or don't understand fully the amswers that are already out there.

I have 2 tables.

Table 1 = T1.

Columns ID1, ID2, TITLE
Value    1, D1, TITLE1
         2, D2, TITLE2
         3, D3, TITLE3
         .....

Table 2 = T2.

Columns ID1, Rev, CODE
Value    1, 1, V1
         1, 2, V2
         1, 3, V3
         1, 4, V4
         2, 1, V5
         2, 2, V6
         2, 3, V7
         3, 1, V8

Ideally I want to return the max value of Rev from T2, toegther with column ID2 from T1. So the results should look like this.

Columns T1.ID2, T1.TITLE, T2.REV, T2.CODE
            D1, TITLE1, 4, V4 
            D2, TITLE2, 3, V6
            D3, TITLE3, 1, V8

This is my attempt and overall I think I am on the right path but it could be some messed up syntax.

SELECT T1.ID2, T1.TITLE, 
        SUBSTRING(

                SELECT MAX(T2.REV)
                FROM  T2 T2B
                WHERE T2B.ID1 = T1.ID1)
                AS MAXREV, 
    T2.CODE
FROM T1, T2
WHERE T1.ID1 = T2.ID1
ORDER BY 1, 2 DESC

Any advice would be greatly appreciated.

Cheers.

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? Postgres? Oracle? — , Jul 27 '17 at 05:11

score 1 · Answer 1 · answered Jul 27 '17 at 05:35

1

 with maxt2 as 
(
 select id1,max(rev) as maxrev
 from t2
 group by id1
)
select t1.id2,t1.title,maxt2.maxrev
from maxt2 m join t1 t 
on m.id1=t.id1
order by 1,2 desc

answered Jul 27 '17 at 05:35

Aparna

286
1
11

If you are using TSQL this should give the results that you require – Aparna Jul 27 '17 at 05:35
For this specific problem (where you can guarantee that you only have one row that is the maximum value) this is the better solution. I jumped into the deep end with mine ;) – Jeff Breadner Jul 27 '17 at 05:38

score 0 · Answer 2 · answered Jul 27 '17 at 05:27

This solution uses T-SQL (MS SQL Server) specific syntax, in fact it only works in SQL Server 2008 and later.

For a MySQL solution, I just posted a solution using the same technique here: How to select latest date from this query (not in a existing table)? with guidance on this method from here: ROW_NUMBER() in MySQL

The T-SQL solution is (including setting up table vars):

declare @t1 table (id1 int, id2 varchar(10), title varchar(20));
declare @t2 table (id1 int, rev int, code varchar(20));

insert into @t1 (id1, id2, title) values (1, 'D1', 'TITLE1');
insert into @t1 (id1, id2, title) values (2, 'D2', 'TITLE2');
insert into @t1 (id1, id2, title) values (3, 'D3', 'TITLE3');

insert into @t2 (id1, rev, code) values (1, 1, 'V1');
insert into @t2 (id1, rev, code) values (1, 2, 'V2');
insert into @t2 (id1, rev, code) values (1, 3, 'V3');
insert into @t2 (id1, rev, code) values (1, 4, 'V4');
insert into @t2 (id1, rev, code) values (2, 1, 'V5');
insert into @t2 (id1, rev, code) values (2, 2, 'V6');
insert into @t2 (id1, rev, code) values (2, 3, 'V7');
insert into @t2 (id1, rev, code) values (3, 1, 'V8');


with
ranked_revisions as (
select
  id1, 
  rev, 
  code,
  row_number() over (partition by id1 order by rev desc) as rowid
from 
  @t2 t2
),

latest_revisions as (
select
  id1,
  rev,
  code
from
  ranked_revisions
where
  rowid = 1
)

select
  t1.id2, 
  t1.title, 
  t2.rev, 
  t2.code
from
  @t1 t1
  inner join latest_revisions t2 on
    t1.id1 = t2.id1

This returns:

id2 title   rev code
D1  TITLE1  4   V4
D2  TITLE2  3   V7
D3  TITLE3  1   V8

The longer answer though is that there is a lot of variability in how to get this row_number() functionality in different flavors of SQL. The overall strategy is to get a row number (partitioned on each category) starting with 1 for the row you want to keep, then just query out those rowid = 1 rows.

The subqueries (CTE's, here) only run once per query run, not once per row, so they're not much of a burden from a performance perspective.

Apart from the `@t1` table name, this is pretty much standard SQL and will work with any modern DBMS. — , Jul 27 '17 at 05:35

SQL : Getting max ID row within a substring

2 Answers2