Getting the last item in a joined table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Other Forums

MS Access

Getting the last item in a joined table?

Author

Topic

Dan Petitt
Starting Member

2 Posts

Posted - 2002-07-12 : 15:27:32

I have two tables: tblItem and tblItemHistory. There are many tblItemHistory records to one tblItem.

If I say get me Title from tblItem with ID=1 then that is fine.
If I say get me Date from tblItemHistory when tblItem ID=1 then I get all the records back from tblItemHistory that match ID=1.

How do I say get me just the 'last' Date from tblItemHistory when tblItem ID=1 and all items in tblItemHistory are sorted by the Date, so I just get one record returned?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-12 : 15:37:03

SELECT TOP 1 i.Field1, i.Field2, ih.field1, ih.Field2
FROM tblItem i
INNER JOIN tblItemHistory ih
WHERE i.ID = 1
ORDER BY ih.Date DESC

I think that should work for you.

Michael

<Yoda>Use the Search page you must. Find the answer you will.

Dan Petitt
Starting Member

2 Posts

Posted - 2002-07-12 : 15:46:31

Sure thanks for that it works in the simple example I have given, but (stupid me, my fault for making the example to specific) it doesnt for what I actually want.

i.e. how do I return all records from Items table and just the 'last' History record for each Item returned. TOP 1 wont work cos it will only return one record completely, and not just 1 record from the joined table.

I know this is not sql but it may help to explain...

SELECT i.Field1, i.Field2, ih.field1, ih.Field2
FROM tblItem i
INNER JOIN TOP 1 tblItemHistory ih
ORDER BY ih.Date DESC

Its a tricky question to ask and I have done some searching without much success, cos it is quite specific. I think I would have to use GROUP BY and HAVING but am not sure.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-12 : 19:22:49

Ok, I think we can do that.

It would be MUCH easier to write if you gave us some sample tables with sample data, and a sample result sets.

This might work though:

SELECT i.Field1, i.Field2, (SELECT TOP 1 date FROM ItemHistory WHERE ItemID = i.ItemID ORDER BY date DESC) as ItemHistoryDate
FROM tblItem i
WHERE i.ID = 1
ORDER BY ih.Date DESC

Michael

<Yoda>Use the Search page you must. Find the answer you will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources