Join?, or something else - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join?, or something else

Author

Topic

Learning
Starting Member

4 Posts

Posted - 2004-11-03 : 16:22:22

Actually, it looked so simple.

Two tables

- Categories
* cat_id
* cat_name

- Articles
* article_id
* article_title
* cat_id

I need to generate a result set where for each category I get only ONE article (the latest one)

so:
categories:
1 cat1
2 cat2
3 cat3

articles:
1 test1 3
2 test2 3
3 test3 1
4 test4 3
5 test5 1
6 test6 1

Would have to return:

cat1 - test6
cat3 - test4

But ehm......HOW?????, I'm crunching like crazy, I just can't work it out :(

THANKS!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-03 : 16:34:14

select max(article_title) as article_title, cat_id
from articles
group by cat_id

if you need to select more columns do and inner join with this query to the article tagle.

select t1.*
from articles t1 inner join (upper select statament) t2 on t1.article_title = t2.article_title and t1.cat_id = t2.cat_id

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 16:35:37

Which one should be returned? The one with the lowest article ID? the highest? Remember, computers are smart but they need to be given very specific instructions on exactly what you want them to do.

- Jeff

Learning
Starting Member

4 Posts

Posted - 2004-11-03 : 16:52:57

Well, I'm trying to return the one with the highest ID.

Gonna try some more here :)

THNX!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 16:57:25

spirit has given you the answer ....

- Jeff

Learning
Starting Member

4 Posts

Posted - 2004-11-03 : 17:07:24

:(, that just doesn't seem to work, putting the max on title though :(

since my sample was just a fiction example

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-03 : 22:56:07

quote:
Originally posted by Learning

:(, that just doesn't seem to work, putting the max on title though :(

since my sample was just a fiction example

how about providing the real ddl and some sample data? or was your problem solved already?

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

Learning
Starting Member

4 Posts

Posted - 2004-11-03 : 23:01:37

Thanks!, actually forgot to post my solution to it, it seems to work, thank you all!

SELECT TOP 100 PERCENT t.article_id, dbo.adw_article_category.article_category_name, t.article_article_category_id, t.article_title
FROM dbo.adw_article t INNER JOIN
dbo.adw_article_category ON t.article_article_category_id = dbo.adw_article_category.article_category_id
WHERE (t.article_id IN
(SELECT MAX(article_id) AS article_id
FROM dbo.adw_article
GROUP BY article_article_category_id)) AND (t.article_approved = 1)
ORDER BY t.article_id DESC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-04 : 04:14:02

a join will be faster....

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources