SQL Query...

scargill

I have a table like this...
ID Date Other Data
AAA 15/01/05 100
AAA 25/04/05 814
BBB 01/04/05 221
CCC 09/10/05 714
CCC 01/01/05 402

And I want to write a create a VIEW that will return rows like this...

ID Date Other Data
AAA 25/04/05 814
BBB 01/04/05 221
CCC 09/10/05 714

Basically, I want to return the latest date for each ID (along with other data from that row). Its Friday afternoon and I'm stumped.

pH

How about

select t.id, t.adate, t.other from table t
where t.adate in ( select max (t2.adate) from table t2 where t.id = t2.id)

other ways to doe this depending on database but thats should be compatible with most versions of sql

scargill

thanks Ph - worked fine - brain was fuzzy on Friday !

madramor

select ID, max(Date), Other Data from table
group by ID.

would be simpler and a lot faster

bonkey

madramor wrote:

would be simpler and a lot faster

Except that it ignores the stated need to retrieve other information from the row in which the max(date) resides.

jc

madramor

bonkey wrote:

Except that it ignores the stated need to retrieve other information from the row in which the max(date) resides.

jc

i can't see your point?

Mutant_Fruit

bonkey wrote:

Except that it ignores the stated need to retrieve other information from the row in which the max(date) resides.

jc

I think the other guys statement would work a lot faster, and do the same thing. You've gone the long way round to writing the exact same code.

seamus

It won't. This is something that had me tearing my hair out for a few minutes a while back.

SELECT col_a, col_b, MAX(col_c) from myTable GROUP BY ID

Will not select values for col_a and col_b in the same row as the value MAX(col_c) occurs. Remember that you don't need a GROUP BY clause to get MAX(col_c).

Imagine you have a table that looks like this:

ID	col_a		col_b		col_c
1	1		3		987		
1	2		6		564		
2	6		2		123		
2	9		5		231

Then the above query is like to return a resultset that looks like this:

ID	col_a		col_b		MAX(col_c)
1	1		3		987		
2	6		2		231

In other words, you find the maximum value, but not the row in which it occurs.

bonkey

madramor wrote:

i can't see your point?

Once you use group by, all retrieved values must be either using a grouping function or must be part of the GROUP BY, yes?

So the "Other Data" that you refer to...how can you ensure its from the same row the Max() has selected a record from?

seamus

Sorry, corrected my above post (it was late, OK?)

pH

All this seems dependent on that database engine, some engines inist (is it part of the ANSI 92 standard?) that all columns in the select clause are either aggregate function or included in the group by.

As the OP didn't mention the database engine, the code I posted does work (though it will have problems with 2 rows having the same max date for an ID - it will include both!)

Is anyone saying that :

SELECT col_a, col_b, MAX(col_c) from myTable GROUP BY ID

will work correctly on ANY database? I'd be amazed if it did, but go on and amaze me! It seems that madramor and Mutant_Fruit do indeed say it works, if so what engine?

madramor

bonkey wrote:

So the "Other Data" that you refer to...how can you ensure its from the same row the Max() has selected a record from?

i see it now in the OP it would give

ID Date Other Data
AAA 25/04/05 100 // not 814
BBB 01/04/05 221
CCC 09/10/05 714

scargill

This is the view that I wanted to create so I could do a join with another table for a report....pH's solution worked for me !!

(MS SQL Server)

CREATE VIEW dbo.view_acquisition_latest
AS
select a1.acq_horse_id, a1.acq_date, a1.acq_orig_cost, a1.acq_currency_code, a1.acq_vendor
from acquisition a1
where a1.acq_date in (select max(a2.acq_date) from acquisition a2 where a1.acq_horse_id = a2.acq_horse_id)