SQL Server Performance Forum – Threads Archive

Difficulty with a querry

Hi guys! I’m doing a news website. We have different type of news like sports, science, technology etc… For some reasons I need to retrieve the first two row of certain type of news.
Example: I want to retrieve the first 2 row of those 2 TypeId ==> 6 and 11 The structure of my table(tblNews) is:
IDint40
Publishbit10
PublishDatedatetime80
[Language]bit10
TypeIdint41
Provincevarchar250
Titlevarchar2500
Headervarchar7501
Authorvarchar2500
Bodyntext160
Hitsint40
UserIDint41
[Order]int40 and for News_Types
TypeIDint40
Namevarchar1000
Descriptionvarchar2501 I seriously don’t know what to do Thanks to help Pascal

---

This will give you 2 rows, and they might be type 6 or type 11, or both – anyway there will just be two: select top 2 * from tblNews where newstype = 6 or newstype = 11 This will give you 2 rows of each type: select top 2 * from tblNews where newstype = 6
union all
select top 2 * from tblNews where newstype = 11 Next thing is that you want to get the "top" 2, not just the first 2 rows that SQL finds somewhere in the table where the newstype happens to be correct. You need to add an ORDER BY, like so: select top 2 * from tblNews where newstype = 6 order by [Order] DESC
union all
select top 2 * from tblNews where newstype = 11 order by [Order] DESC

---

Sorry my explanation was bad, I need to retrieve the first 2 row of each TypeId but we don’t know how many TypeId will be sent. The beginning of my querry will look like this: CREATE PROCEDURE sp_News_GetSpecific
@Sections varchar(50)
AS
….
@Sections is all the TypeId I will have to retrieve the first 2 row.
@Section might be 4,11 or 6,12 or 4,6,11,12 etc… Somewhere in your querry you will need a TypeId IN (4,11,12) by example Thanks to answer Pascal

---

Basically, you can do something like: CREATE PROCEDURE sp_News_GetSpecific
@Sections varchar(50)
AS EXEC (‘select * from tblNews t where t.newstype IN (‘
+ @Sections
+ ‘) AND t.ID IN
(SELECT TOP 2 x.ID FROM tblNews x WHERE x.newstype = t.newstype
ORDER BY x.[Order] DESC)’) GO
There is some stuff that you need to be aware of with "dynamic SQL" – do a search here, and you’ll find plenty of discussions.

---

Very good this is working!!! Thank you Adriaan! Pascal

---

I think it is not possible to union two queries which has Order by
You can Order it as a whole select top 2 * from tblNews where newstype = 6 order
union all
select top 2 * from tblNews where newstype = 11 order by [Order] DESC or Select * from
(
select top 2 * from tblNews where newstype = 6
union all
select top 2 * from tblNews where newstype = 11
) T
order by [Order] DESC Madhivanan Failing to plan is Planning to fail

---

Hi Madhivanan, thanks for checking. Silly that it’s not supported in plain T-SQL, but there’s no problem if you use derived tables … However, to get the two most recent entries from both categories, you must use ORDER BY in the derived tables, no way around that! SELECT * FROM (select top 2 * from tblNews where newstype = 6 order by [Order] DESC) T1
union all
SELECT * FROM (select top 2 * from tblNews where newstype = 11 order by [Order] DESC) T2

---

Yes it is. I wonder why Order by union in queries are not directly supported [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

]]>