Not quite sure how to do this...

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

SQL Server Development (2000)

Not quite sure how to do this...

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2006-12-04 : 01:17:49

Say you have these two tables:

items
-------------
-id
-title
-setid
-cdate

item_sets
-------------
-id
-title

Would it be possible to write a query where you are returned the top 4 most recent images of each set?

pravin14u
Posting Yak Master

246 Posts

Posted - 2006-12-04 : 01:23:05

Do you need the 4 most recent records that has been inserted?

You can use identity for id's and can use the following query

select top 4 * from each table order by id's desc

AdamKosecki
Starting Member

18 Posts

Posted - 2006-12-04 : 01:33:17

Right, I know that. I was hoping to return, in one query, the four most recent items (based on "cdate") in each item_set.

Example:

If I wanted the four most recent items in item_set.id = 3, I could do this:

SELECT TOP 4 *
FROM items
WHERE setid = 3
ORDER BY cdate DESC

But I want the four most recent from every item set, back in one query. Mehopes.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 01:51:36

a group by perhaps?

--------------------
keeping it simple...

AdamKosecki
Starting Member

18 Posts

Posted - 2006-12-04 : 02:13:11

I.... think you're missing the point, or I'm missing something obvious.

There a lot of items. For each item there is a set_id. I want to return the four most recent items for each unique setid. If I were to accomplish this in psuedocode, I would do this:

arrayOfIds = "SELECT id FROM item_sets";

foreach (arrayOfIds as curId) {
SELECT TOP 4 id FROM items WHERE setid = curId ORDER BY cdate DESC
}

That would execute many queries to get the desired result. I would like to do it in one query.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 03:04:33

can you post some sample data?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources