Naming column from aggregated query 1

[fredodman]

Hi y'all!

I'm new to all of this so my question is probably dumb but I'll risk it anyway since I can't find my answer anywhere. I want to fill a Gridview from a sql query and all is well except for one field. The MAX function is needed (I think) to get the last date from a table but I cannot name the new column with the AS clause. When I run the query in SQL Manager it produces the correct result but the column I'm after says (No column name) at the top. Any thoughts on how to assign a name to this column.

I'll include the query below.

SELECT DISTINCT ContactExtendedProperties.ActiveStatus, Contacts.LastName, Contacts.FirstName,
ContactExtendedProperties.WelcomeCard, ContactExtendedProperties.BeforePic, ContactExtendedProperties.AfterPic,
NeedsTable.Description, ContactExtendedProperties.CurrentStatus, ContactExtendedProperties.SpacesCompleted,
ContactExtendedProperties.ClientLongevity,
(SELECT MAX(ContactFollowup.DateOfFollowup) AS DateOfFollowup FROM ContactFollowup),
ContactExtendedProperties.ReceivedEval, ContactExtendedProperties.OncancellationList
FROM Contacts
INNER JOIN ContactExtendedProperties ON Contacts.ContactId = ContactExtendedProperties.ContactId
INNER JOIN ClientStatus ON Contacts.ClientStatusId = ClientStatus.StatusId
INNER JOIN ContactFollowup ON Contacts.ContactId = ContactFollowup.ContactId
INNER JOIN RO_Users ON Contacts.PrimaryConsultantId = RO_Users.UserId
INNER JOIN ContactToNeeds ON Contacts.ContactId = ContactToNeeds.ContactId
INNER JOIN NeedsTable ON ContactToNeeds.NeedsId = NeedsTable.NeedId
WHERE Clientstatus.StatusDesc LIKE '%Client%'

Any help is appreciated.

Fred

 

[RiverGuy]

SELECT DISTINCT ContactExtendedProperties.ActiveStatus, Contacts.LastName, Contacts.FirstName,
ContactExtendedProperties.WelcomeCard, ContactExtendedProperties.BeforePic, ContactExtendedProperties.AfterPic,
NeedsTable.Description, ContactExtendedProperties.CurrentStatus, ContactExtendedProperties.SpacesCompleted,
ContactExtendedProperties.ClientLongevity,
(SELECT MAX(ContactFollowup.DateOfFollowup) AS DateOfFollowup FROM ContactFollowup) AS [b]MaxDateOfFollowup[/b],
ContactExtendedProperties.ReceivedEval, ContactExtendedProperties.OncancellationList
FROM Contacts
INNER JOIN ContactExtendedProperties ON Contacts.ContactId = ContactExtendedProperties.ContactId
INNER JOIN ClientStatus ON Contacts.ClientStatusId = ClientStatus.StatusId
INNER JOIN ContactFollowup ON Contacts.ContactId = ContactFollowup.ContactId
INNER JOIN RO_Users ON Contacts.PrimaryConsultantId = RO_Users.UserId
INNER JOIN ContactToNeeds ON Contacts.ContactId = ContactToNeeds.ContactId
INNER JOIN NeedsTable ON ContactToNeeds.NeedsId = NeedsTable.NeedId
WHERE Clientstatus.StatusDesc LIKE '%Client%'

That's how you name it, but I think you need to look at your query closer. That subquery will return the max date from the table regardless of what contact id it is.

 

[gmmastros]

>> but I cannot name the new column with the AS clause

You can, just not the way you were doing it. Let me show you...

SELECT DISTINCT ContactExtendedProperties.ActiveStatus, 
       Contacts.LastName, 
       Contacts.FirstName,
       ContactExtendedProperties.WelcomeCard, 
       ContactExtendedProperties.BeforePic, 
       ContactExtendedProperties.AfterPic,
       NeedsTable.Description, 
       ContactExtendedProperties.CurrentStatus, 
       ContactExtendedProperties.SpacesCompleted,
       ContactExtendedProperties.ClientLongevity,
       (SELECT MAX(ContactFollowup.DateOfFollowup) [!][s]AS DateOfFollowup [/s][/!]
               FROM ContactFollowup) [!]As DateOfFollowup[/!],
       ContactExtendedProperties.ReceivedEval, 
       ContactExtendedProperties.OncancellationList
FROM Contacts
INNER JOIN ContactExtendedProperties ON Contacts.ContactId = ContactExtendedProperties.ContactId
INNER JOIN ClientStatus ON Contacts.ClientStatusId = ClientStatus.StatusId
INNER JOIN ContactFollowup ON Contacts.ContactId = ContactFollowup.ContactId
INNER JOIN RO_Users ON Contacts.PrimaryConsultantId = RO_Users.UserId
INNER JOIN ContactToNeeds ON Contacts.ContactId = ContactToNeeds.ContactId
INNER JOIN NeedsTable ON ContactToNeeds.NeedsId = NeedsTable.NeedId
WHERE Clientstatus.StatusDesc LIKE '%Client%'

Technically speaking, you are using a subquery to get the max date. You were naming the column within the subquery (which isn't necessary) and then NOT aliasing the column outside of the subquery.

By the way, subqueries are usually slower than derived tables, and it appears as though you are not filtering the subquery so I STRONGLY encourage you to make sure you are getting the correct results. It's entirely possible that your query is correct because I don't know that nature of your table structure.

Does this make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

A derived table approach that is probably more accurate and runs faster (especially with larger tables).

SELECT DISTINCT ContactExtendedProperties.ActiveStatus, 
       Contacts.LastName, 
       Contacts.FirstName,
       ContactExtendedProperties.WelcomeCard, 
       ContactExtendedProperties.BeforePic, 
       ContactExtendedProperties.AfterPic,
       NeedsTable.Description, 
       ContactExtendedProperties.CurrentStatus, 
       ContactExtendedProperties.SpacesCompleted,
       ContactExtendedProperties.ClientLongevity,
       [!]MaxFollowupDate.MaxDate As DateOfFollowup,[/!]
       ContactExtendedProperties.ReceivedEval, 
       ContactExtendedProperties.OncancellationList
FROM   Contacts
       INNER JOIN ContactExtendedProperties ON Contacts.ContactId = ContactExtendedProperties.ContactId
       INNER JOIN ClientStatus ON Contacts.ClientStatusId = ClientStatus.StatusId
       INNER JOIN [!](
          Select ContactId, MAx(DateOfFollowup) As MaxDate
          From   ContactFollowup 
          Group By ContactId
          ) As MaxFollowupDate ON Contacts.ContactId = MaxFollowupDate.ContactId[/!]
       INNER JOIN RO_Users ON Contacts.PrimaryConsultantId = RO_Users.UserId
       INNER JOIN ContactToNeeds ON Contacts.ContactId = ContactToNeeds.ContactId
       INNER JOIN NeedsTable ON ContactToNeeds.NeedsId = NeedsTable.NeedId
WHERE  Clientstatus.StatusDesc LIKE '%Client%'

I encourage you to try this query. If this works for you, and you would like me to explain it, just let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredodman]

Wow, you guys are fast. Thank you so much for your help. RiverGuy, you are of course correct, that is not the result from the table I was after in the first place. Thank you for that one and thank you George, I will try the drrived approach that you suggested. Once again, thank you both so much.

Fred

 

[fredodman]

You guys are the best. Two minutes of copy and paste and you solved something I've been looking at for hours. My hat is off to the true pro's.

Fred

 

[gmmastros]

PPpppfffffttttt!!!!!!

I don't care if 2 minutes of copy/paste solved your problem. It's more important to me that you understand the query. I think I explained it well enough, but if there is anything that you don't understand regarding the query, please speak up.

Derived tables are a very powerful concept that you can use to create better/faster queries. It's important to be able to recognize where they can be utilized within larger queries. Truly understanding derived tables will make you a better programmer. Again, if you have any questions about it, let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredodman]

Sorry George!

You are of course correct that I should take it in and learn from your advice but I was so happy to get this solved. I have looked at the code now and it actually make sense to me. I have a few questions though if you do have a minute to help me out.
Just a wee bit of background, I did not create this database and have never worked with one until now. I'm helping a friend who's developer/administrator left for other projects.
Questions.
Is the derrived table destroyed after the query is done?
Is it possible to have a where statement when I select values for the derrived table?
Also something unrelated to this question.

I have three tables with ContactId in A, ContactId and OtherId in B, and OtherId in C. Is it possible to take multiple values from C and present them in one column in some sort of comma separated field?

Thanks for helping again George and sorry if I offended you with my copy and paste comment. I have come a long way in a few weeks thanks to helpful people like yourself having no experience with sql at all before.

Yet again. Thank you.

Fred

 

[gmmastros]

>> Is the derived table destroyed after the query is done?

The derived table never really existed in the first place. So there's nothing to destroy. You can think of it as a virtual table, or a pretend table. Whatever. Really... it just another way to embed a query within another. It's not the same as a sub-query so try not to confuse them.

Is it possible to have a where statement when I select values for the derived table?

Yes. But be aware that there are multiple ways of implementing the where clause. Your original question (about the column aliases) was a very good question. As you can see, the placement of the alias is very important. Similarly, the placement of the where clause is important to.

Suppose you wanted to run this query for a single contact. There are various ways that you can use a where clause.

Suppose you had this query...

SELECT Columns....,
       MaxFollowupDate.MaxDate As DateOfFollowup
FROM   Contacts
       INNER JOIN (
          Select ContactId, MAx(DateOfFollowup) As MaxDate
          From   ContactFollowup 
          Group By ContactId
          ) As MaxFollowupDate ON Contacts.ContactId = MaxFollowupDate.ContactId
WHERE  Contacts.ContactId = 1

This is filtering the derived table (sort of). You see... the Max followup date will be calculated for every unique ContactId. Then, you INNER JOIN to the contact table on contact id, and finally filter it on Contact.ContactId. Because you are inner joining and filtering, you will only get rows (from the derived table) for the contact id in the where clause. This query will do exactly what you expect it to do. Unfortunately, it will do more (behind the scenes) than it needs to. It WILL calculate the Max followup date for each contactId, but then filter out rows you don't want.

Instead, you could put a where clause in the derived table so that it only calculates the max date for the contact you want. Like this....

SELECT Columns...,
       MaxFollowupDate.MaxDate As DateOfFollowup
FROM   Contacts
       INNER JOIN (
          Select ContactId, MAx(DateOfFollowup) As MaxDate
          From   ContactFollowup 
          [!]WHERE  Contacts.ContactId = 1[/!]
          Group By ContactId
          ) As MaxFollowupDate ON Contacts.ContactId = MaxFollowupDate.ContactId
WHERE  Contacts.ContactId = 1

Now, only the max date will be calculated for one contact. SQL Server has less work to do, and the query will perform faster. All this, and you still get the results you want.

>> some sort of comma separated field

Yes again. This has been asked many times (you're not surprised, are you?).

Read this...
thread183-1159740



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

There was a mistake in that last query. Sorry. It should have been....

SELECT Columns...,
       MaxFollowupDate.MaxDate As DateOfFollowup
FROM   Contacts
       INNER JOIN (
          Select ContactId, MAx(DateOfFollowup) As MaxDate
          From   ContactFollowup 
          [!]WHERE[/!]  [blue]ContactFollowup[/blue][!].ContactId = 1[/!]
          Group By ContactId
          ) As MaxFollowupDate ON Contacts.ContactId = MaxFollowupDate.ContactId
WHERE  Contacts.ContactId = 1

I copy/pasted the outer where clause and forgot to change the table name.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredodman]

George, you are a superstar. Not only have you explained in a way that I actually understand, you have me realized that I want to learn more about this stuff. Since I'm new to this it didn't even occured to me that there are more than one way to do things. I'll read the link that you provided about the other stuff and we'll see if I understand that part as well.

Thanks again.

Fred