SQL aggregate fuction and GROUP BY

[Roumen Semov]

Hello, everyone!
Does anyone know how I can pull additional field in a database when
the max() of one field is pulled. For example:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
Here I am trying to pull the records that have the latest date for
each foreign_id. The result set above will pull foreign_id and
max_recordcreat eddatetime but I needed to also have it display one
more field, current_status like this:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime, current_status
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
The problem however is that SQL wants me to add this field to GROUP BY
or use an aggregate function with it and I don't want any aggregate
function processing - I just want current_status to show up there for
me to see. How do I do this?
Thank you for any input in advance!
Roumen.

[Garry]

> SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS

max_recordcreat eddatetime, current_status
FROM table1
GROUP BY foreign_id

select top 200
t1.foreign_id
,t2.max_recordc reateddatetime
,t1.urrent_stat us
from table1 t1
inner join (select
foreign_id
,max_recordcrea teddatetime = MAX(recordcreat eddatetime)
FROM table1
GROUP BY foreign_id) t2 on t1.foreign_id = t2.foreign_id

[Steve Kass]

Roumen,

This technique works:

select foreign_id, recordcreatedda tetime, current_status
from table1 T1A
where recordcreatedda tetime = (
select max(recordcreat eddatetime) from table1 T1B
where T1B.foreign_id = T1A.foreign_id
)

as does this:

select foreign_id, recordcreatedda tetime, current_status
from table1 T1A
where not exists (
select * from table1 T1B
where T1B.foreign_id = T1A.foreign_id
and T1B.recordcreat eddatetime > T1A.recordcreat eddatetime
)

I've left out TOP 200, since without an ORDER BY clause, the result is indeterminate.

Steve Kass
Drew University


Roumen Semov wrote:

Hello, everyone!
Does anyone know how I can pull additional field in a database when
the max() of one field is pulled. For example:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
Here I am trying to pull the records that have the latest date for
each foreign_id. The result set above will pull foreign_id and
max_recordcreat eddatetime but I needed to also have it display one
more field, current_status like this:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime, current_status
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
The problem however is that SQL wants me to add this field to GROUP BY
or use an aggregate function with it and I don't want any aggregate
function processing - I just want current_status to show up there for
me to see. How do I do this?
Thank you for any input in advance!
Roumen.

[Roumen Semov]

Thank you guys,
I was never aware that I could refer to elements in the main query from subqueries.
Roumen.
Steve Kass <sk***@drew.edu > wrote in message news:<uf******* *******@newsrea d3.news.atl.ear thlink.net>...

Roumen,

This technique works:

select foreign_id, recordcreatedda tetime, current_status
from table1 T1A
where recordcreatedda tetime = (
select max(recordcreat eddatetime) from table1 T1B
where T1B.foreign_id = T1A.foreign_id
)

as does this:

select foreign_id, recordcreatedda tetime, current_status
from table1 T1A
where not exists (
select * from table1 T1B
where T1B.foreign_id = T1A.foreign_id
and T1B.recordcreat eddatetime > T1A.recordcreat eddatetime
)

I've left out TOP 200, since without an ORDER BY clause, the result is indeterminate.

Steve Kass
Drew University


Roumen Semov wrote:

Hello, everyone!
Does anyone know how I can pull additional field in a database when
the max() of one field is pulled. For example:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
Here I am trying to pull the records that have the latest date for
each foreign_id. The result set above will pull foreign_id and
max_recordcreat eddatetime but I needed to also have it display one
more field, current_status like this:
=============== =============== =============== ========
SELECT TOP 200 foreign_id, MAX(recordcreat eddatetime) AS
max_recordcreat eddatetime, current_status
FROM table1
GROUP BY foreign_id
=============== =============== =============== ========
The problem however is that SQL wants me to add this field to GROUP BY
or use an aggregate function with it and I don't want any aggregate
function processing - I just want current_status to show up there for
me to see. How do I do this?
Thank you for any input in advance!
Roumen.