HOW TO: Select MAX(T2.Id) of T2 for a given value T2.Value?

Question

Select all rows in @t2 where the latest/last entry (i.e. MAX(@t2.Id))

SomeValue is a given value, e.g. 4

declare @t1 table (Id int not null primary key)
declare @t2 table (Id int not null primary key, Table1Id int not null, SomeValue int not null)

INSERT @t1 VALUES (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113)

INSERT @t2 VALUES (1,100,1),(5,100,2),(9,100,4),(10,100,5),
(2,101,1),(6,101,2),(11,101,4),(13,101,7),
(3,102,1),(7,102,2),(12,102,4),(14,102,6),
(15,103,1),(17,103,2),
(16,104,1),(18,104,2),(19,104,4),
(20,105,1),(25,105,2),(27,105,4),(28,105,7),
(21,106,1),
(22,107,1),(29,107,2),
(23,108,1),(30,108,2),
(31,109,1),
(32,110,1),(36,110,2),(40,110,3),
(33,111,1),(37,111,2),(44,111,3),
(34,112,1),(38,112,2),(43,112,4),
(35,113,1),(41,38,2),(42,39,4)

you want to select all rows in @t2 where (condition), what is the use of @t1? — Kryptonian, Sep 04 '15 at 19:25
is you are looking for somthing like this : select * from L1 where L1.id = (select MAX(L2.id) from L2) — Jophy job, Sep 04 '15 at 19:36
translating your L1 for t1 and L2 for t2, I get no results: select * from t1 t1 where t1.id = (select MAX(t2.id) from t2 t2) — Trober, Sep 04 '15 at 19:40
What I need is the *last entry* for each t2.Table1Id. From that, I can WHERE it for a given t2.SomeValue. e.g. Select all t2.Table1Id's where the last SomeValue entered for it is 4. ("last" = MAX(t2.Id), t2.Id is auto-incrmented). — Trober, Sep 04 '15 at 19:42
my bad, last row of t2 entries should be (35,113,1),(41,113,2),(42,113,4) — Trober, Sep 04 '15 at 19:48
Do you need this, Select * from t2 where id =(select max(t2.id) from t1 join t2 on t1.id = t2.table1id); , I would suggest editing the question by adding the info you have provided in comments. — Kryptonian, Sep 04 '15 at 19:51
This gets me the max SomeValue for each Table1Id: select Table1Id, max (SomeValue) SomeValue from t2 group by Table1Id But, I need to now filter that for a given SomeValue, e.g. all SomeValue's of 4. d'oh — Trober, Sep 04 '15 at 19:55
@learningNew I no longer have an edit button for the question. :-( — Trober, Sep 04 '15 at 19:56
Something like this to get all SomeValue=4, but correct sql syntax: select * from ( select Table1Id, max (SomeValue) AS SomeValue from @t2 group by Table1Id ) where SomeValue = 4 — Trober, Sep 04 '15 at 19:57
I got the answer from this: http://stackoverflow.com/questions/4629979/nested-select-statement-in-sql-server select * from ( select Table1Id, max (SomeValue) AS SomeValue from @t2 group by Table1Id ) t where SomeValue = 4 — Trober, Sep 04 '15 at 19:59

score 0 · Answer 1 · answered Sep 04 '15 at 20:10

Here's the answer, a simple sub-select:

declare @t1 table (Id int not null primary key) declare @t2 table (Id int not null primary key, Table1Id int not null, SomeValue int not null)

INSERT @t1 VALUES (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113)

INSERT @t2 VALUES (1,100,1),(5,100,2),(9,100,4),(10,100,5), (2,101,1),(6,101,2),(11,101,4),(13,101,7), (3,102,1),(7,102,2),(12,102,4),(14,102,6), (15,103,1),(17,103,2), (16,104,1),(18,104,2),(19,104,4), (20,105,1),(25,105,2),(27,105,4),(28,105,7), (21,106,1), (22,107,1),(29,107,2), (23,108,1),(30,108,2), (31,109,1), (32,110,1),(36,110,2),(40,110,3), (33,111,1),(37,111,2),(44,111,3), (34,112,1),(38,112,2),(43,112,4), (35,113,1),(41,113,2),(42,113,4)

select * from ( select Table1Id, max (SomeValue) AS SomeValue from @t2 group by Table1Id ) t where SomeValue = 4

HOW TO: Select MAX(T2.Id) of T2 for a given value T2.Value?

1 Answers1