SQL statement help

[levinll]

I would greatly appreciate it if someone could help me formate the SQL code I need to return only the records I want.

Here's the scenario. I have a table that consists of records that contain a integer and a date/time. There can be a one to many relationship between the integer field and the date/time field. For Example:

Integer Date
---------------------
3 10/2/2002 10:34am
3 10/2/2002 10:34am
3 10/2/2002 3:32pm
3 10/2/2002 3:32pm
4 10/3/2002 5:50pm
4 10/3/2002 5:50pm
4 10/3/2002 5:50pm
5 10/7/2002 3:34pm

I'm trying to write an SQL statement so for each integer in the table, I get the records that have th latest date in the date field.

Any help in this area would be greatly appreciated.

 

[MeanGreen]

Try this:

select [integer],max([date]) from table
group by [integer]

Hope this helps

 

[levinll]

I'm halfway home. The table i set up was a simplied version of what I am actually using. The table I'm actually using has many other fields in it. I want to return several other fields, but still using the logic you provided above.

Do I have to add each field to my GROUP BY statement, and if so, what order do they have to be in ?

Thanks !

 

[MeanGreen]

Yes... add them to your select statement and then to your group by. The group by is from left to right in order of precedence. You might also want to include an Order by if you want the list sorted.

Hope this helps.

 

[levinll]

I haven't had a chance to re-visit this since yesterday, but it appears that my celebration was premature.

Using your suggestion:
select [integer],max([date]) from table
group by [integer]

Is only returning one record.

using my sample data:
ID Date
------------------------------------
3 10/2/2002 10:34am
3 10/2/2002 10:34am
3 10/2/2002 3:32pm
3 10/2/2002 3:32pm
4 10/3/2002 5:50pm
4 10/3/2002 5:50pm
4 10/3/2002 5:50pm
5 10/7/2002 3:34pm

I would want the following records returned if I passed the value of 3 to my stored procedure:
3 10/2/2002 3:32pm
3 10/2/2002 3:32pm

 

[MeanGreen]

Looks to me that you do not want max, but want unique items. Try this:

select [integer],[date] from table
where [integer] = 3
group by [integer],[date]

Also remember that a datetime datatype also has miliseconds. If you want your query to return to nearest minute, then you will have to do a few more steps.

Hope this helps.