GROUP BY issue - 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

General SQL Server Forums

New to SQL Server Programming

GROUP BY issue

Author

Topic

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-26 : 19:53:39

Hello Gurus,

I realize this is a noob question with an easy answer...

(Using Access2007)

The Background:
I have a table (Interaction Table) that has the columns DatabaseID, InteractionNum (primary key), UTD (boolean), and Date. There are multiple rows for any given DatabaseID.

What I'm trying to get:
A query that finds the largest Date per DatabaseID, and displays that result with that row's UTD & InteractionNum values.

To try to do this I am using the following string:

SELECT it.DatabaseID, it.InteractionNum, Max(it.Date) AS MaxOfDate FROM [Interaction Table] AS it WHERE (it.InteractionType='Visit' Or it.InteractionType='Post-Visit Call') And (it.Date Between #1/1/2008# And #12/31/2008#) GROUP BY it.DatabaseID, it.InteractionNum;

The issue:
I cannot get my result because the GROUP BY syntax, which from what I know is necessary in queries containing aggregate functions, is causing multiple DatabaseID rows to appear.

(said in another way...)

If the query GROUP BY contains only one column, it works fine. But when I try to add the other columns (UTD and InteractionNum) I have to group them too. In the case of UTD, I would then get two rows, the max Date for a row with True, and another with False.

How can I just display these additional rows without it altering the results?

Thank you for your time....

-V

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 21:07:22

Try this

SELECT DatabaseID,InteractionNum,UTD,Date
from (SELECT *,rank() over(partition by DatabaseID order by Date desc as ranknum) t
where t.ranknum = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-26 : 22:36:05

quote:
Originally posted by vijayisonly

Try this

SELECT DatabaseID,InteractionNum,UTD,Date
from (SELECT *,rank() over(partition by DatabaseID order by Date desc as ranknum) t
where t.ranknum = 1

small modification ur Query

SELECT DatabaseID,InteractionNum,UTD,Date
from (SELECT *,rank() over(partition by DatabaseID order by Date desc )as ranknum from urtable ) t
where t.ranknum = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-26 : 22:39:01

Try this One also

select it.databaseid,it.interactionnum,maxdate from
[Interaction Table] AS it
inner join ( select databaseid,max(date) from [Interaction Table] WHERE (it.InteractionType='Visit' Or it.InteractionType='Post-Visit Call') group by databaseid ) t on t.databaseid = it.databaseid

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-26 : 22:59:33

Thank you vijayisonly for your initial post. I tried it and there was an error in the FROM section.

Thank you Nageswar9 for your edits... I'll try those, see how it goes, and post back!

-V

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-26 : 23:43:10

Nageswar9:

The last one you posted was the only on that I culd get to run, but while the subquery filtered out the records correctly, the primary query added them back in again (only this time with the maxDate instead of their normal date.

I'll try to build it again and see if it works, otherwise I may resort to Dlookup calls on each of the InteractionNum for the extra info.

Thank you!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-27 : 09:12:21

VIgilante...this shud work...

SELECT DatabaseID,InteractionNum,UTD,Date
from (SELECT *,rank() over(partition by DatabaseID order by Date desc )as ranknum from urtable ) t
where t.ranknum = 1

if not, can you post thre query that gave you the error.

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-27 : 15:37:59

Hi vijayisonly:

The SQL I used is this:

SELECT DatabaseID, InteractionNum, UTD, Date
FROM (SELECT *, rank() over(partition by DatabaseID ORDER BY Date DESC) AS ranknum FROM [Interaction Table] ) t
WHERE t.ranknum = 1;

The error is this:
Syntax error (missing operator)in query expression 'rank() over(partition by DatabaseID order by Date desc)'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-27 : 22:36:31

[code]SELECT t.*
FROM [Interaction Table]t
Inner join
(SELECT DatabaseID,InteractionNum,Max(date)as Maxdate
FROM [Interaction Table]
WHERE (InteractionType='Visit' Or InteractionType='Post-Visit Call') And (Date Between '01/1/2008' And '12/31/2008')
Group by DatabaseID,InteractionNum )Z
On t.DatabaseID = Z.DatabaseID and t.date = Z.Maxdate[/code]

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-28 : 01:00:55

It still doesn't work, maybe an issue with Access Query builder? I'll try another method... thank you for your advice!

Vigilante001
Starting Member

6 Posts

Posted - 2009-02-28 : 17:12:44

Hi SoDeep:

That string works, but it pulls each unique InteractionNum, so it pulls...

DatabaseID__|_InteractionNum__|___MaxOfDate
____998____________3427__________2/13/2008
____1001____________1634__________1/1/2008
____1002____________1967__________3/24/2008
____1002____________3427__________3/24/2008
____1005____________3427__________6/27/2008
____1008____________3427__________5/6/2008

Where without the InteractionNum in GROUP BY, the duplicate DatbaseID 1002 isn't included, and looks like...

DatabaseID__|___MaxOfDate
____998__________2/13/2008
____1001__________1/1/2008
____1002__________3/24/2008
____1005__________6/27/2008
____1008__________5/6/2008

Now, here is the kicker. I got it to work the way I want, but I think it is sloppy. It works because by calling the InteractionNum as an aggregate, I don't need to GROUP it, and it doesn't pull the extra line. THAT is what I have been trying to get all along, and I think the aggregate method is too backwards logic.
This is the code:


SELECT i.DatabaseID, First(i.InteractionNum) AS ITNumber, Max(i.Date) AS MaxOfDate 
FROM [Interaction Table] as i
WHERE ((i.InteractionType IN ('Visit','Post-Visit Call')) AND (i.Date Between #1/1/2008# And #12/31/2008#))
GROUP BY i.DatabaseID;

It works in this case because the Max(Date) leaves only one InteractionNum to pull from, but it may not work in a different table & data. Isn't there a way of just displaying a field in the same rowline on the other rows pulled without including it in the GROUP BY?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources