Group by question - 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 question

Author

Topic

igor92128
Starting Member

23 Posts

Posted - 2011-12-22 : 15:36:32

I have a problem with my query, here's the pseudocode:

select distinct t.customer_num,
t.last_name,
t.first_name,
...
...
o.B_ID
max(o.DT_Created)
from mastertable t
join Obj o on xxx.o_id = o.o_id
// 5 or so more joins here
group by t.last_name, t.first_name, ..., o.B_ID <---

Now, whenever I take away the o.B_ID from the group by clause, I get the 1 single max(o.DT_Created) which is correct, but I need to o.B_ID in the select also. Whenever I include it, all the dates are returned though. Any ideas on how to include the o.B_ID and have the max(o.DT_created) work at the same time? Maybe there is a simple solution?

Thanks,
Igor

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 15:44:33

that's because you are getting the max date now for o.B_ID

What is it that you actually want

sample data and expected results would be a big help

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

igor92128
Starting Member

23 Posts

Posted - 2011-12-22 : 16:39:33

Well the Obj table contains rows O_ID, B_ID, DT_Created

Customer_Num is primary key in mastertable
I am doing about 5 joins and joining Obj table where O_ID is the primary key, but this is not the problem

I am trying to join mastertable to Obj and only get the record where max(o.DT_Created) and include the value of B_ID from Obj table also.

Here is some sample data:

1. When I DONT include o.B_ID in the group by (only the max record is returned which is correct but I need the B_ID from Obj in the resultset.

Customer_Num Last_Name First_Name B_ID DT_Created
<guid> Smith John <null> 2011-5-3

2. When I DO include o.B_ID in the group by - now I am getting all the dates, and the MAX is ignored:

Customer_Num Last_Name First_Name B_ID DT_Created
<guid> Smith John <guid1> 2011-2-3
<guid> Smith John <guid2> 2011-4-3
<guid> Smith John <guid3> 2011-5-3

Any Ideas?

Thanks,
Igor

igor92128
Starting Member

23 Posts

Posted - 2011-12-22 : 17:07:53

The expected result should be:

Customer_Num Last_Name First_Name B_ID DT_Created
<guid> Smith John <guid3> 2011-5-3

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-22 : 18:24:08

Here is one way:

SELECT *
FROM
    (
        select 
            t.customer_num,
            t.last_name,
            t.first_name,
            ...
            ...
            o.B_ID,
            o.DT_Created,
            ROW_NUMBER() OVER (PARTITION BY o.o_id ORDER BY o.DT_Created DESC) AS RowNum
        from mastertable t
        join Obj o on xxx.o_id = o.o_id
        // 5 or so more joins here
    ) AS T
WHERE
    RowNum = 1

igor92128
Starting Member

23 Posts

Posted - 2011-12-22 : 18:42:30

Thanks, but is it also possible to just do an update afterwards and leave the original query as is? I am creating a temp table and doing an insert before the select but I didn't show it in the pseudocode.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-23 : 11:41:53

quote:
Originally posted by igor92128

Thanks, but is it also possible to just do an update afterwards and leave the original query as is? I am creating a temp table and doing an insert before the select but I didn't show it in the pseudocode.

Sure you can. Do you need help with writing the update statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources