Need help for a querry - 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

SQL Server Development (2000)

Need help for a querry

Author

Topic

pharvey
Starting Member

20 Posts

Posted - 2006-01-12 : 12:43:34

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!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 14:05:24

You can do this using nested subqueries. You will need two levels because of your composite key.

select	tblNews.CategoryType,
	tblNews.[ID],
	min(tlbNews.[Order]) as [Order]
from	tblNews
	inner join --FirstNewsIDs
		(SELECT	tblNews.CategoryType,
			min(tblNews.[ID]) as [ID]
		from	tblNews
		group by tblNews.CategoryType) FirstNewsIDs
		on tblNews.CategoryType = FirstNewsIDs.CategoryType
		and tblNews.[ID] = FirstNewsIDs.[ID]
where	tlbNews.TypeID = 15
group by tblNews.CategoryType,
	tblNews.[ID]
order by tbleNews.[Order]

pharvey
Starting Member

20 Posts

Posted - 2006-01-13 : 05:02:57

Nice try blindman and thank you to help me but it doesn't work if the order doesn't follow the ID's.

If i have this on my tables

Category ID Order
DDRRR 465 1
DDRRR 444 2
Economy 240 1
Politics 4279 1
Politics 4281 2
Politics 4282 3

Whith your query I will receive:

DDRRR 444 1
Economy 240 1
Politics 4279 1

But the good result is:

DDRRR 465 1
Economy 240 1
Politics 4279 1

Someone?????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 05:08:57

Can you post the structure for tblNews, tblCategories and also sample data for these 2 tables ? as specify in http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx. It will makes thing easier for all of us.

-----------------
'KH'
if you can't beat them, have someone else to beat them

pharvey
Starting Member

20 Posts

Posted - 2006-01-13 : 05:46:19

The structure of my tables are:

[tblNews]
ID int
Publish bit
PublishDate datetime
TypeId int
CategoryType int
Title varchar
Body ntext

[tblCategories]
CategoryID int
Name varchar
Description varchar

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 08:33:07

pharvey, please read the post of khtan carefully

quote:
Originally posted by khtan

.........and also sample data ...............

It will makes thing easier for all of us.

-----------------
'KH'

I mean
the table creation queries &
the insert statements
that v can run and create the tables & fill ur tables.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-13 : 09:34:26

quote:
Originally posted by pharvey

Nice try blindman and thank you to help me but it doesn't work if the order doesn't follow the ID's.

If i have this on my tables

Category ID Order
DDRRR 465 1
DDRRR 444 2
Economy 240 1
Politics 4279 1
Politics 4281 2
Politics 4282 3

Whith your query I will receive:

DDRRR 444 1
Economy 240 1
Politics 4279 1

But the good result is:

DDRRR 465 1
Economy 240 1
Politics 4279 1

Someone?????

You are laboring under the common misconception that the data in your table has some inherant order, but the order of data in a database has no meaning. You seem to want to return the "first" record for each group, but the database has no concept of "first" without an ORDER BY clause. Even a clustered index or primary key does not guarantee that the data will be processed or returned in any particular order.
So, you will need some sort of column in your table to indicate the order of the data. Either an incrementing Identity value or a datetime field.

pharvey
Starting Member

20 Posts

Posted - 2006-01-13 : 10:25:49

Thanks all for your help. I forget to put the [Order] field on the tblNews table. Sorry about it.
I found a way to do my query:

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 TOP 1 T1.ID FROM tblNews T1
WHERE T1.CategoryType = tblNews.CategoryType
AND T1.TypeID = 15
ORDER BY T1.[Order]
)
ORDER BY tblNews.[Order]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources