Help with Select Distinct - 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

Transact-SQL (2000)

Help with Select Distinct

Author

Topic

WebDeveloper
Starting Member

4 Posts

Posted - 2006-05-23 : 19:41:12

I am trying to display a list of the most recently updated Photo Categories. "Most recently updated" means "Contains the newest photos". But my statement returns a list of the most recent 14 photos, all in the same category. It *should* return a list of 5 categories ordered by the date of the last photo in that category.

select distinct top 5 pc.categoryid,p.datecreated 
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
order by p.datecreated desc

What it's currently returning:

categoryid datecreated
---------- -----------
104	   2006-05-22 01:04:03.280
104	   2006-05-22 01:04:03.090
104	   2006-05-22 01:04:02.577
104	   2006-05-22 01:04:02.357
104	   2006-05-22 01:04:02.187

An example of what it *should* be returning:

categoryid datecreated
---------- -----------
104        2006-05-22 01:04:03.280
89         2006-05-21 01:04:03.280
105        2006-05-20 01:04:03.280
4          2006-05-19 01:04:03.280
200        2006-05-18 01:04:03.280

Thank you,

Aaron

-----
http://CodeMe.com - Where coders go for answers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 19:56:24

select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY p.datecreated
order by p.datecreated desc

Tara Kizer
aka tduggan

WebDeveloper
Starting Member

4 Posts

Posted - 2006-05-23 : 20:02:02

quote:
Originally posted by tkizer

select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY p.datecreated
order by p.datecreated desc

Tara Kizer
aka tduggan

I receive the following error when attempting to execute this script in Query Analyzer:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'pc.categoryid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Aaron

-----
http://CodeMe.com - Where coders go for answers

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-23 : 20:51:56

Try
select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY ~~p.datecreated~~ pc.categoryid
order by p.datecreated desc

if it doesn't work, try the following:

select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY ~~p.datecreated~~ pc.categoryid
order by MAX(p.datecreated) desc

Srinika

WebDeveloper
Starting Member

4 Posts

Posted - 2006-05-23 : 20:58:16

quote:
Originally posted by Srinika

Try
select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY ~~p.datecreated~~ pc.categoryid
order by p.datecreated desc

if it doesn't work, try the following:

select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY ~~p.datecreated~~ pc.categoryid
order by MAX(p.datecreated) desc

Srinika

They both work, thank you! Can I ask you which one is more efficient and/or scalable? Or just direct me to a method of testing to figure out the results for myself.

Thanks again!

Aaron

-----
http://CodeMe.com - Where coders go for answers

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-23 : 21:07:14

Actually I didn't do much, except correcting a typo error in an earlier post.

I think the one without Max function in Order By would be more efficient (because of not calling that function)

Srinika

WebDeveloper
Starting Member

4 Posts

Posted - 2006-05-23 : 21:27:29

Okay great, thanks again!

Aaron

-----
http://CodeMe.com - Where coders go for answers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 21:59:33

quote:

select pc.categoryid, MAX(p.datecreated) AS datecreated
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY pc.categoryid
order by p.datecreated desc

The above code is actually order the result by the alias name datecreated and not by column datecreated of table photos (p.datecreated)

This can be seen by changing the alias name to be different from the column name.

select pc.categoryid, MAX(p.datecreated) AS date_created
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY pc.categoryid
order by p.datecreated desc

And you will encounter the following error.
"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."

It better if you use the alias name in the ORDER BY without table name prefix (as the below).

select pc.categoryid, MAX(p.datecreated) AS date_created
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY pc.categoryid
order by date_created desc

The following will works on SQL Server 2000 (think 2000 is more forgiving then 2005) but not in 2005 as date_created is not a column name of table photos.

select pc.categoryid, MAX(p.datecreated) AS date_created
from photos_categories pc
inner join photos p on pc.categoryid=p.categoryid
GROUP BY pc.categoryid
order by p.date_created desc

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-22 : 07:42:45

Hello Frnds,
is it possible without using Row number function the reason i am looking to implement some other logic other than Row_number() because query is fired on a History table which contains crores of records... so query takes long time to execute.
and that too i want to show them category wise.

So pls help me out...

Thanks & Regards,
Girish Chhatani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-22 : 07:46:28

Girish

please post your question as a new thread and post it in appropriate forum

KH
[spoiler]Time is always against us[/spoiler]

GirishChhatani
Starting Member

7 Posts

Posted - 2012-02-22 : 08:20:29

thnks for the Concern...
if have added it into a new topic called

SQL Server 2008 Forums->Transact-SQL (2008)->Query get last 3 records per ID & category wise

Thanks & Regards,
Girish Chhatani

jacoboram75
Starting Member

3 Posts

Posted - 2012-03-06 : 01:33:03

I think the one without Max function in Order By would be more efficient (because of not calling that function)

-----------------------------
http://www.marjinalescort.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources