Issues with Group By and Order By in one statement

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)

Issues with Group By and Order By in one statement

Author

Topic

jamonakes
Starting Member

18 Posts

Posted - 2007-09-09 : 15:37:37

Hello, I am trying to use group by to display the following in one line.


--SELECT TOP 1 Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Posted, Articles.Article_Comments_Count, Articles.Article_ID FROM (FB_Categories Categories LEFT JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID) LEFT JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID WHERE Category_order= 0 ORDER BY Category_Order, Articles.Article_Posted DESC;
--SELECT TOP 1 Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Posted, Articles.Article_Comments_Count, Articles.Article_ID FROM (FB_Categories Categories LEFT JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID) LEFT JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID WHERE Category_order= 1 ORDER BY Category_Order, Articles.Article_Posted DESC;
--SELECT TOP 1 Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Posted, Articles.Article_Comments_Count, Articles.Article_ID FROM (FB_Categories Categories LEFT JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID) LEFT JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID WHERE Category_order= 2 ORDER BY Category_Order, Articles.Article_Posted DESC;

When I try the following, it works, but is unable to order by Articles.Article_Posted DESC, and instead orders by Category_Title for some reason which I cant understand, and if I introduce the column category_order in the order by, it misfires


SELECT TOP 3 Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Comments_Count, Articles.Article_ID  FROM (FB_Categories Categories LEFT JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID) LEFT JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID GROUP BY Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Posted, Articles.Article_Comments_Count, Articles.Article_ID  ORDER BY count(*);

Tables schema are:
FB_Articles:


CREATE TABLE [FB_Articles] (
	[Article_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Article_User_ID] [int] NOT NULL ,
	[Article_Category_ID] [int] NOT NULL ,
	[Article_Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Article_Excerpt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Article_Content] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Article_Posted] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Article_Comments] [int] NOT NULL CONSTRAINT [DF__FB_Articl__Artic__2645B050] DEFAULT (1),
	[Article_Comments_Count] [int] NOT NULL CONSTRAINT [DF__FB_Articl__Artic__2739D489] DEFAULT (0),
	[Article_Images] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Article_Level] [int] NOT NULL CONSTRAINT [DF__FB_Articl__Artic__282DF8C2] DEFAULT (0),
	CONSTRAINT [PK_Article_ID] PRIMARY KEY  CLUSTERED 
	(
		[Article_ID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_FB_Categories_FB_Articles] FOREIGN KEY 
	(
		[Article_Category_ID]
	) REFERENCES [FB_Categories] (
		[Category_ID]
	),
	CONSTRAINT [FK_FB_Users_FB_Articles] FOREIGN KEY 
	(
		[Article_User_ID]
	) REFERENCES [FB_Users] (
		[User_ID]
	)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

FB_Categories:


CREATE TABLE [FB_Categories] (
	[Category_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Category_Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Category_Order] [int] NOT NULL ,
	CONSTRAINT [PK_Category_ID] PRIMARY KEY  CLUSTERED 
	(
		[Category_ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Any help will be greatly appreciated

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-10 : 09:33:36

It's ordering by count(*) is that what you want (isn't it always 1?)?
Do you really want the 3 with the least number of entries ordered by Article_Posted desc?

try

select Category_Title, Article_Title, Article_Excerpt, Article_Comments_Count, Article_ID
from
(
SELECT TOP 3 Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Comments_Count, Articles.Article_ID, Articles.Article_Posted
FROM FB_Categories Categories
LEFT JOIN FB_Articles Articles
ON Categories.Category_ID = Articles.Article_Category_ID
LEFT JOIN FB_Users Users
ON Users.User_ID = Articles.Article_User_ID
GROUP BY Category_Title, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Posted, Articles.Article_Comments_Count, Articles.Article_ID, Articles.Article_Posted
ORDER BY count(*);
) a
order by Article_Posted DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources