Get Just The Latest
-
There is a badly designed Caché database I have to grab data from via ODBC. Rather than updating a table, they decided to just append the new records. (Won't they be surprised, eventually. :p) Anyway, is there a simple-ish way to get just the latest row for each set of records? Some sort of cool groupy join or something? "ID" is an autoincrememnt int Col2 is a varchar that identifies the set of records Col3 is the data I'm ultimately interested in. (There are other columns, of course, but probably irrelevant for this question...I hope) Looks sorta like this:
ID Col2 Col3
1 A 23
2 A 23
3 A 23
4 A 24
5 B 17
6 B 18
7 B 19
8 C 01
9 C 01What I want to end up with is
4 A 24
7 B 19
9 C 01No dogs or cats are in the classroom. My Mu[sic] My Films My Windows Programs, etc.
-
There is a badly designed Caché database I have to grab data from via ODBC. Rather than updating a table, they decided to just append the new records. (Won't they be surprised, eventually. :p) Anyway, is there a simple-ish way to get just the latest row for each set of records? Some sort of cool groupy join or something? "ID" is an autoincrememnt int Col2 is a varchar that identifies the set of records Col3 is the data I'm ultimately interested in. (There are other columns, of course, but probably irrelevant for this question...I hope) Looks sorta like this:
ID Col2 Col3
1 A 23
2 A 23
3 A 23
4 A 24
5 B 17
6 B 18
7 B 19
8 C 01
9 C 01What I want to end up with is
4 A 24
7 B 19
9 C 01No dogs or cats are in the classroom. My Mu[sic] My Films My Windows Programs, etc.
This view might work
create or replace view grouped_table as
select col2
,max(ID)
from table
group by col2
;Then you query the table and join on the view
select t.ID
,t.col2
,t.col3
from table t
,grouped_table gt
where t.id = gt.idThis is just off the top of my head, though. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
This view might work
create or replace view grouped_table as
select col2
,max(ID)
from table
group by col2
;Then you query the table and join on the view
select t.ID
,t.col2
,t.col3
from table t
,grouped_table gt
where t.id = gt.idThis is just off the top of my head, though. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Ah... Probably should I said I don't own the database, can't create anything on it, etc. Not even a temporary table/view. :(
No dogs or cats are in the classroom. My Mu[sic] My Films My Windows Programs, etc.
In that case try this then
select t.ID
,t.col2
,t.col3
from table t
,( select col2
,max(ID) id
from table
group by col2
) gt
where t.id = gt.idAgain, off the top of my head. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
There is a badly designed Caché database I have to grab data from via ODBC. Rather than updating a table, they decided to just append the new records. (Won't they be surprised, eventually. :p) Anyway, is there a simple-ish way to get just the latest row for each set of records? Some sort of cool groupy join or something? "ID" is an autoincrememnt int Col2 is a varchar that identifies the set of records Col3 is the data I'm ultimately interested in. (There are other columns, of course, but probably irrelevant for this question...I hope) Looks sorta like this:
ID Col2 Col3
1 A 23
2 A 23
3 A 23
4 A 24
5 B 17
6 B 18
7 B 19
8 C 01
9 C 01What I want to end up with is
4 A 24
7 B 19
9 C 01No dogs or cats are in the classroom. My Mu[sic] My Films My Windows Programs, etc.
Using this table: create table tt ( id int, Col2 varchar(1), Col3 Int) This seems to work for me ...
select * from TT where id in (
select max(id) from tt group by col2) -
In that case try this then
select t.ID
,t.col2
,t.col3
from table t
,( select col2
,max(ID) id
from table
group by col2
) gt
where t.id = gt.idAgain, off the top of my head. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Looks like it's working. Thanks. Won't know for sure, though, until it finishes...if it does. Started it a little over three hours ago and it began delivering the goods about two hours after that. Oh...did I forget to mention this $%^$@#! table has over 177 million rows in it? And I expect the result to be in the neighborhood of 3 million? Hopefully, it will finish before I go home this afternoon. Otherwise, pfft! "Night Watchman" will come along at 3 a.m. and shut off my computer and I'll have to start all over again in the morning. :laugh: EDIT: :laugh: :laugh: :laugh: It finished. And, as soon as I clicked on the result to try to copy it, BOOM! 'So sorry. We've run out of virtual memory and have to close. Hope you weren't working on anything too terribly important'. Or words to that effect.
No dogs or cats are in the classroom. My Mu[sic] My Films My Windows Programs, etc.