Most recent date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Most recent date

Author

Topic

Sweet16
Starting Member

15 Posts

Posted - 2011-09-21 : 12:23:02

I have a Rate table with the following fields: AutoID, Company Name, Rate, Date.

I would like to create a query that provides the most updated rate for each company based on the date. I have come across a view suggestions but am having a little difficulty since I am a novice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:25:01

[code]
SELECT r.*
FROM Rate r
INNER JOIN (SELECT CompanyName,MAX(Date) AS Latest
FROM Rate
GROUP BY CompanyName)r1
ON r1.CompanyName=r.CompanyName
AND r1.Latest = r.Date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sweet16
Starting Member

15 Posts

Posted - 2011-09-21 : 12:29:57

Thanks for the quick resposne. I am fairly new at access. Can you explain the r's and *? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:36:34

what i've given is query that works in sql server as you've posted in sql forum. Please post in Access forum if this is not working and you want access specific solution.
r is alias (short name for table). you can replace * with actual column names you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sweet16
Starting Member

15 Posts

Posted - 2011-09-21 : 12:47:42

This worked perfectly, thank you ???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:48:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sweet16
Starting Member

15 Posts

Posted - 2011-09-21 : 16:34:32

I have an additional question. I have Company table that includes company inforamtion such as the name and address. When I utilize the aforementioned query and try to join it with the company table I get all of the records and not those that are the most recent. Is there any solution to this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 22:09:03

how are joining to company table ie using what all fields? is there multiple addresses existing for a company in that table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sweet16
Starting Member

15 Posts

Posted - 2011-09-22 : 10:44:54

I figured out what was happening. I inserted the above query:
SELECT r.*
FROM Rate r
INNER JOIN (SELECT CompanyName,MAX(Date) AS Latest
FROM Rate
GROUP BY CompanyName)r1
ON r1.CompanyName=r.CompanyName
AND r1.Latest = r.Date

which works great. However, when i went to design view to add additional fields I keep getting an error message that states access can't represent the join expression r1.Latest=r.Date. After I clicked ok I could see the Rate table and r1 table in design view but when I clicked back to table view I would see all of the records (not just the most recent). I have not idea how or why this is happening. I basically have to go in and recopy the query. Once I do this it works out.

eager2lrn
Starting Member

1 Post

Posted - 2011-09-22 : 11:26:43

I am also having a similiar issue. I don't have the same fields as you do but my overall objective is the same. I am creating a Grade table that keeps track of student's grades with information for test, grade, and date. A student a test more than once but I only want their most recent grades to appear to attach to thier profile. Their prfile is in another table Profile that includes their age, school grade, and teacher. I'm having problems withi this as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 11:40:32

quote:
Originally posted by Sweet16

I figured out what was happening. I inserted the above query:
SELECT r.*
FROM Rate r
INNER JOIN (SELECT CompanyName,MAX(Date) AS Latest
FROM Rate
GROUP BY CompanyName)r1
ON r1.CompanyName=r.CompanyName
AND r1.Latest = r.Date

which works great. However, when i went to design view to add additional fields I keep getting an error message that states access can't represent the join expression r1.Latest=r.Date. After I clicked ok I could see the Rate table and r1 table in design view but when I clicked back to table view I would see all of the records (not just the most recent). I have not idea how or why this is happening. I basically have to go in and recopy the query. Once I do this it works out.

dont use view designer. better use syntax like below to create the view


CREATE VIEW yourViewName
AS
SELECT r.*
FROM Rate r
INNER JOIN (SELECT CompanyName,MAX(Date) AS Latest
            FROM Rate
            GROUP BY CompanyName)r1
ON r1.CompanyName=r.CompanyName
AND r1.Latest = r.Date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources