help adding line onto query - 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 adding line onto query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-13 : 20:24:28

Hi,

I have a query thats been running fine the past few years, but I need to add a tiny bit of functionality onto it. Currently the link I am working with looks like this

ISNULL((SELECT MAX(dateApproved) FROM tblPhotoApproval_log WHERE approved = 1 and photoID = 0), getDate()) as newUser_LastApprovedTime

Instead of just bringing back the MAX(dateApproved) column, I now want to bring back "moderatorID" from the same table (which I will use to join onto another table). What is the best way to do this ? Its looking like I might have to take a pretty different approach.

Just adding the column to the SELECT list produces this result:

Server: Msg 116, Level 16, State 1, Procedure select_count_maintenance, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thanks very much for any assistance anyone can provide. This ones probably pretty straight forward for you vets, but its got me stumped.

Cheers,
Mike

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-13 : 22:55:40

You need to think of a few things here.
1) How many rows will the max() return when you add the new column? You can now get several rows back when several moderatorIDs have the same dateApproved
2) If it's more than one (because dateApproved/moderatorID is not a unique constraint) what does that mean to your data set?
3) What does it mean to the question you are asking

I think answer is to select the moderator ids where dateApproved=max(dateApproved) and then exists/join to that but I think you have some thinking to do yourself before you'll be able to get the answer.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-14 : 00:20:49

Hi LoztinSpace,

Thanks for response.

1) I want to run the MAX function, purely on the "dateApproved" column.

2) If there are 10 rows with the same dataApproved, its not really a big deal which one comes back. The main purpose is to get the dateApproved, which moderatorID is not crucial information. Most times there won't be same dates and when there is, its no big deal.

Hope this helps!

Thanks very much
mike123

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-14 : 04:20:35

You will still need to make the decision about which moderator whether you like it or not, or else your query will probably fail to do what you want when you get multiples.
If it really doesn't matter then you can use TOP 1 or MAX/MIN if it's a PK
Without your full query it will be hard to write it but something like a nested select of

select MAX(moderatorID), ISNULL(dateApproved, getDate()) from tblPhotoApproval_log where
ISNULL(dateApproved, getDate())=ISNULL((SELECT MAX(dateApproved) FROM tblPhotoApproval_log WHERE approved = 1 and photoID = 0), getDate())

So get the max moderatorID and the dateApproved where the date approved is the biggest.
Something like that anyway.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources