Help with query with ORDER BY clause

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

General SQL Server Forums

New to SQL Server Programming

Help with query with ORDER BY clause

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 15:57:50

Here's my schema:

-----------------------------------------
tblBrands
-----------------------------------------
BrandID | BrandName
-----------------------------------------

-----------------------------------------
tblProducts
-----------------------------------------
ProductID | BrandID | DateCreated
-----------------------------------------

I have many different brands. Each brand contains several products. I'd like to output all brands, but ordered by the DateCreated of products within the brand. So if a brand contains a product that was just created yesterday, it would be at the top of the list.

Here's my query:

SELECT b.BrandID, b.BrandName, p.DateCreated, Count(b.BrandID) as [Count]
FROM tblBrands b INNER JOIN tblProducts p on
p.BrandID=b.BrandID
GROUP BY b.BrandID, b.BrandName, p.DateCreated
HAVING Count(b.BrandID) > 0
ORDER BY p.DateCreated DESC

The only problem is that if a brand contains 5 products, it will output the brand 5 times. I only need to output it once, how can I fix this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 16:05:00

Do not group on DateCreated. Instead use an aggregate function for that one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 16:10:34

quote:
Originally posted by tkizer

Do not group on DateCreated. Instead use an aggregate function for that one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

What function should I use?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 16:19:10

It depends on which DateCreated you want to see returned. Perhaps MAX is what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-01 : 16:20:47

Instead of p.DateCreated use dateadd(day,datediff(day,0,p.DateCreated),0)

Madhivanan

Failing to plan is Planning to fail

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 16:24:54

quote:
Originally posted by tkizer

It depends on which DateCreated you want to see returned. Perhaps MAX is what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hmmm...it's still returning duplicate brands. Let me show my full query, I left some of it out for simplicity, but maybe something is interfering:

SELECT b.BrandID, b.BrandName, MAX(p.DateCreated), COUNT(b.BrandID) as [Count]
FROM tblBrands b
INNER JOIN tblProducts p ON p.BrandID=b.BrandID
WHERE b.VisibilityID=1 AND p.VisibilityID=1 AND
p.DateCreated >= '8/28/2007 6:33:31 PM' AND
p.DateCreated <= '8/28/2008 6:33:31 PM'
GROUP BY b.BrandID, b.BrandName, p.DateCreated
HAVING COUNT(b.BrandID) > 0
ORDER BY p.DateCreated DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-01 : 16:35:18

If you use MAX(p.DateCreated) you should omit it from Group by and Order by clause
Did you try as I suggested?

Madhivanan

Failing to plan is Planning to fail

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 16:39:47

quote:
Originally posted by madhivanan

Instead of p.DateCreated use dateadd(day,datediff(day,0,p.DateCreated),0)

Madhivanan

Failing to plan is Planning to fail

Where? In the select clause or the order clause?

quote:

Originally posted by madhivanan

If you use MAX(p.DateCreated) you should omit it from Group by and Order by clause

I can't omit it from the order by clause, I have to order it by p.DateCreated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 16:45:14

quote:
Originally posted by Apples

Hmmm...it's still returning duplicate brands. Let me show my full query, I left some of it out for simplicity, but maybe something is interfering:

SELECT b.BrandID, b.BrandName, MAX(p.DateCreated), COUNT(b.BrandID) as [Count]
FROM tblBrands b
INNER JOIN tblProducts p ON p.BrandID=b.BrandID
WHERE b.VisibilityID=1 AND p.VisibilityID=1 AND
p.DateCreated >= '8/28/2007 6:33:31 PM' AND
p.DateCreated <= '8/28/2008 6:33:31 PM'
GROUP BY b.BrandID, b.BrandName, p.DateCreated
HAVING COUNT(b.BrandID) > 0
ORDER BY p.DateCreated DESC

You didn't remove DateCreated from the group by like I mentioned in my first reply.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 16:48:03

quote:
Originally posted by tkizer

quote:
Originally posted by Apples

...

You didn't remove DateCreated from the group by like I mentioned in my first reply.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Oops! OK, I removed it from the GROUP BY clause, and it gave me this error:

Level 16, State 1, Line 1
Column name 'p.DateCreated' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Here's my query now:

SELECT b.BrandID, b.BrandName, MAX(p.DateCreated), COUNT(b.BrandID) as [Count]
FROM tblBrands b
INNER JOIN tblProducts p ON p.BrandID=b.BrandID
WHERE b.VisibilityID=1 AND p.VisibilityID=1 AND
p.DateCreated >= '8/28/2007 6:33:31 PM' AND
p.DateCreated <= '8/28/2008 6:33:31 PM'
GROUP BY b.BrandID, b.BrandName
HAVING COUNT(b.BrandID) > 0
ORDER BY p.DateCreated DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-01 : 16:48:36

Well you need to add the aggregate to your order by too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2008-07-01 : 16:58:38

Yay! Thank you Tara, that worked perfectly. I was banging my head against the wall.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources