SQL Server Performance Forum – Threads Archive

Need help for a query

I have this querry:
SELECT tblCategories.[Name], tblNews.[ID], tblNews.[Order]
FROM tblNews LEFT JOIN tblCategories ON tblNews.CategoryType = tblCategories.CategoryID
WHERE tblNews.TypeID = 15
GROUP BY tblCategories.[Name], [ID], tblNews.[Order]
ORDER BY tblNews.[Order] The result of this querry is: DDRRR 444 1
DDRRR 465 2
Economy 240 1
Politics 4279 1
Politics 4281 2
Politics 4282 3 I would like to receive the first row of each category(tblCategories.[Name]) with the other informations. NB, the IDs received are not from the same table. The results must look like:
DDRRR 444 1
Economy 240 1
Politics 4279 1 Someone can help on this one please!

---

SELECT tblCategories.[Name], tblNews.[ID], min(tblNews.[Order])
FROM tblNews LEFT JOIN tblCategories ON tblNews.CategoryType = tblCategories.CategoryID
WHERE tblNews.TypeID = 15
GROUP BY tblCategories.[Name], tblNews.[ID]

---

Thanks to try mmarovic!
But the result is the same.

---

Try a correlated subquery: SELECT tblCategories.[Name], tblNews.[ID], tblNews.[Order]
FROM tblNews
LEFT JOIN tblCategories ON tblNews.CategoryType = tblCategories.CategoryID
WHERE tblNews.TypeID = 15
AND tblNews.ID =
(SELECT MIN(T1.ID) FROM tblNews T1
WHERE T1.CategoryType = tblNews.CategoryType
AND T1.TypeID = 15)
GROUP BY tblCategories.[Name], [ID], tblNews.[Order]
ORDER BY tblNews.[Order]

---

Sorry, I haven’t payed enough attention to your sample. Why do you use left join with table tblCategories? Can categoryType be null? Or maybe you have categoryTypes that don’t have matching row in table tblCategories? Assuming this is an mistake and that ID is tblNews pk:
SELECT tblCategories.[Name], n.[ID], n.[Order]
from (select categoryType, min([ID]) as newsID
FROM tblNews
where typeID = 15
group by categoryType) as t
join tblNews n on t.newsID = n.[id]
JOIN tblCategories c ON t.CategoryType = c.CategoryID

---

We are close to get it Adriaan but your results will return the smaller ID and what we want is the smaller Order. If the order did not follow the newsId you receive the smaller ID. If the result of my firs query was DDRRR 465 1
DDRRR 444 2
Economy 240 1
Politics 4282 1
Politics 4281 2
Politics 4279 3 I want this:
DDRRR 465 1
Economy 240 1
Politics 4282 1 I don’t want this:
DDRRR 444 2
Economy 240 1
Politics 4279 3

---

It works the exact same way: just change the column name from ID to Order. As long as Order is a numeric field, not varchar, you’re fine.

---

Thank you again mmarovic!
You’re right we don’t need the join. The fact is my query is a little bit more complex but I put the essential part of what I want to get. You’re query is working if the ID and the order are well coordinate but it’s not working if they are not following. I want for each Category(tblCategories.[Name]) the last news I have with the lower [Order]. The structure of my tables are: [tblNews]
ID int
Publishbit
PublishDate datetime
TypeId int
CategoryType int
Title varchar
Body ntext [tblCategories]
CategoryID int
Name varchar
Description varchar

---

Like I said: use the correlated subquery, but on Order instead of on ID. mmarovic’s solution is pretty much the same as mine: what he does in the JOIN clause is the same thing that happens in my IN clause.

---

Ok, in that case try: SELECT tblCategories.[Name],
(select top 1 n.[ID]
from tblNews n
where n.order = t.order and n.categoryType = t.categoryType) as [ID],
t.[Order]
from (select categoryType, min(order) as order
FROM tblNews
where typeID = 15
group by categoryType) as t
JOIN tblCategories c ON t.CategoryType = c.CategoryID

---

You are right Adriaan, I started with different understanding of the problem, but after fixies I made they offer the same resultset and pretty much the same performance.

---

I guess you forgot the order column in tblNews.

---

With your helps guys I finally found my query.<br />TY so much! <img src=’/community/emoticons/emotion-2.gif’ alt=’‘ /><br /><br />I didn’t try your last query mmarovic but I will to see wich one have the best performance. TY<br /><br />My query look like this:<br /><br />SELECT tblCategories.[Name], tblNews.[ID], tblNews.[Order]<br />FROM tblNews<br />LEFT JOIN tblCategories ON tblNews.CategoryType = tblCategories.CategoryID<br />WHERE tblNews.TypeID = 15<br />AND tblNews.ID =<br />(<br />SELECT TOP 1 T1.ID FROM tblNews T1<br />WHERE T1.CategoryType = tblNews.CategoryType<br />AND T1.TypeID = 15<br />ORDER BY T1.[Order]<br />)<br />GROUP BY tblCategories.[Name], [ID], tblNews.[Order]<br />ORDER BY tblNews.[Order]

---

You may find that the IN or JOIN versions perform better, but that depends on a number of factors – nr of rows in both tables, indexing, …

---

]]>