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(recordcreateddatetime) AS
max_recordcreateddatetime
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_recordcreateddatetime but I needed to also have it display one
more field, current_status like this:
================================================== ===
SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) AS
max_recordcreateddatetime, 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(recordcreateddatetime) AS

max_recordcreateddatetime, current_status
FROM table1
GROUP BY foreign_id

select top 200
t1.foreign_id
,t2.max_recordcreateddatetime
,t1.urrent_status
from table1 t1
inner join (select
foreign_id
,max_recordcreateddatetime = MAX(recordcreateddatetime)
FROM table1
GROUP BY foreign_id) t2 on t1.foreign_id = t2.foreign_id

[Steve Kass]

Roumen,

This technique works:

select foreign_id, recordcreateddatetime, current_status
from table1 T1A
where recordcreateddatetime = (
select max(recordcreateddatetime) from table1 T1B
where T1B.foreign_id = T1A.foreign_id
)

as does this:

select foreign_id, recordcreateddatetime, current_status
from table1 T1A
where not exists (
select * from table1 T1B
where T1B.foreign_id = T1A.foreign_id
and T1B.recordcreateddatetime > T1A.recordcreateddatetime
)

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(recordcreateddatetime) AS
max_recordcreateddatetime
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_recordcreateddatetime but I needed to also have it display one
more field, current_status like this:
================================================== ===
SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) AS
max_recordcreateddatetime, 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**************@newsread3.news.atl.earthlin k.net>...

Roumen,

This technique works:

select foreign_id, recordcreateddatetime, current_status
from table1 T1A
where recordcreateddatetime = (
select max(recordcreateddatetime) from table1 T1B
where T1B.foreign_id = T1A.foreign_id
)

as does this:

select foreign_id, recordcreateddatetime, current_status
from table1 T1A
where not exists (
select * from table1 T1B
where T1B.foreign_id = T1A.foreign_id
and T1B.recordcreateddatetime > T1A.recordcreateddatetime
)

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(recordcreateddatetime) AS
max_recordcreateddatetime
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_recordcreateddatetime but I needed to also have it display one
more field, current_status like this:
================================================== ===
SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) AS
max_recordcreateddatetime, 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.