get latest row from various column values

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

SQL Server 2000 Forums

Transact-SQL (2000)

get latest row from various column values

Author

Topic

stevewa
Starting Member

2 Posts

Posted - 2008-09-30 : 23:09:26

Hi I have a few tables:

blogs (id, name)

blog_entries (id, blog_id, title, date_posted)

blogs_featured(id, blog_id, display_position)

I am trying to figure out how to get the latest blog_entry from each blog that is in the featured table.

==this returns the blog ids in the fetured table==
select blog_id from blogs_featured order by display_position

==this would return the latest blog entries==
select title, date_posted from blog_entries
where blog_id in (select blog_id from blogs_featured order by display_position)
order by date_posted desc

but I can't figure out how to limit the blog entries to be the latest row amongst the different blog_is values

i've tried distinct, and group by, but I am not sure how to describe this. any help ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:08:19

[code]SELECT b.name,be.title,be.date_posted,bf.display_position
FROM blogs b
INNER JOIN blogentries be
on be. blog_id=b.id
INNER JOIN (SELECT blog_id,MAX(date_posted)AS MaxDate
FROM blogentries
GROUP BY blog_id) be1
on be1.blog_id=be.blog_id
and be1.MaxDate=be.date_Posted
INNER JOIN blogs_featured bf
on bf.blog_id=b.id[/code]

stevewa
Starting Member

2 Posts

Posted - 2008-10-01 : 22:56:35

Thank you Visakh16 ! You are very knowledgeable.

Let me see if I understand this correctly...

/* You choose the fields you want returned at the beginning of your query SELECT statement */

SELECT b.name,be.title,be.date_posted,bf.display_position

/* choose your tables, and join them together using INNER JOIN, according to the relationship between table keys. the order of JOINS matters, because you can't reference a table in the ON statement if it hasn't been joined yet, i.e. be must be joined before you say ON BE.ID, or it wont know what table you are refering to */

FROM blogs b
INNER JOIN blog_entries be
on be.blog_id=b.id

/* this next inner join is creating a dervied table from a query and saving it as be1. this query uses the operation MAX() to select the highest value of the date posted field, (GENIUS - I thought I had to SELECT TOP 1 ... ORDER BY to get the highest value).

this dervied table is grouped by BLOG_ID, which means it groups together rows with the same blog_id value, then it can use the Max operator to get the highest value in that group. then it saves it as a column value MaxDate, which is used later in the query. */

INNER JOIN (SELECT blog_id, MAX(date_posted) AS MaxDate
FROM blog_entries
GROUP BY blog_id) be1
on be1.blog_id=be.blog_id
and be1.MaxDate=be.date_Posted

/* the derived table contains the blog_id of the latest posting, so it is used to limit what is returned from the blog_entries table, so it only returns 1 row from each blog id. the be1.MaxDate=be.date_posted ensures this by returning the latest posting for that blog id. otherwise you would get more than 1 row from the blog_entries table */

INNER JOIN blogs_featured bf
on bf.blog_id=b.id

/* the above inder join includes the BLOGS_FEATURED table using the matching keys, so the correct blog is selected. */

ORDER BY bf.display_position

/* I added an ORDER BY statement so the featured blogs could be displayed in the desired order in the output list, according to display_position values. */

I hope I understood it correctly so I can learn how to do this myself on our projects.

I hope I explained it well enough so that others may also understand an learn.

Thank you visakh16, may the universe smile on you...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources