join and group by - 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 2005 Forums

Transact-SQL (2005)

join and group by

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 05:07:38

Hi,

I have a little problem with my joins and group by query.

I have two tables, A and B.

A has paymentnr, infoid and date.

B has a codetext, codeid.

A and B are related by infoid and codeid.

I want to get paymentnr and codetext for all with the latest date.

I tried with this but x.codetext does not display in the result-table:

SELECT paymentnr, x.codetext
FROM
(
select paymentnr, max([date]) as latestdate
from A
inner join
(select codetext, codeid
from B
group by codetext, codeid) x ON x.codeid = A.infoid
group by paymentnr
) Y
order by paymentnr

It is important to get both paymentnr and codetext, but i dont manage. I know why i cant get codetext out, but the problem i have is how to get around this problem.

Would appreciate any help.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 05:25:57

Try this

SELECT paymentnr, y.codetext
FROM
(
select x.codetext,paymentnr, max([date]) as latestdate
from A
inner join
(select codetext, codeid
from B
group by codetext, codeid) x ON x.codeid = A.infoid
group by paymentnr,codetext
) Y
order by paymentnr

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 05:40:48

ayamas,

Thanks for your reply. However, your query did not solve the problem. The thing is I get duplicates of paymentnr when i use your query. I have used a similar query before. But it does not solve the problem. A paymentnr can have several codetexts that is why I use max(date).

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 05:53:36

Please post some sample data.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 06:35:36

Hi,


select top 3 * from A

ID  Paymentnr   InfoId  Date
1   5000005     1       2008-03-09
2   5000005     2       2008-04-10
3   5000006     1       2008-01-09

ID is a primary key and Identity.


select top 2 * from B

ID	CodeId	CodeText
1	1	texttexttext
2	2	blabla

ID is a primary key and Identity.

The only thing that relates these tables are infoId and CodeId.

The problem with your query was "select x.codetext ..." together with max([date])..this made duplicates for paymentnr.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 06:47:31

Hi,

To make the query smaller, lets try to get PaymentNr, infoId and max([date]) out from table A without duplicates.

For instance, this query below would be helpful, due to paymentNr can have more than one infoId. So it will list max-date together with paymentNr and infoId more than one time for a certain paymentNr if paymentNr has more than one infoId. That is the problem. My wish is to list a certain paymentNr only one time in the result table together with it's infoId, that is why I use max-aggregate function. But it does not help, since I group by both paymentNr and infoId. Any ideas to get around this?


SELECT  paymentnr, infoId, max([date]) as latestdate
FROM A
GROUP BY paymentnr, infoId
ORDER BY paymentnr

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 06:59:14

Create table A(paymentnr varchar(40),infoid int,date datetime)
Create table B(codetext varchar(40),codeid int)

insert into A
select 5000005,1,'2008-03-09'union all
select 5000005,2,'2008-04-10'union all
select 5000006,1,'2008-01-09'

insert into B
select 'a',1 union all
select 'b',2

;With CTE(paymenthr,latestdate,codetext)as
(
SELECT paymentnr,latestdate,codetext
FROM
(
select paymentnr,x.codetext, max([date]) as latestdate
from A
inner join
(select codetext, codeid from B
) x ON x.codeid = A.infoid
group by paymentnr,codetext
) Y
)

select paymenthr,latestdate,codetext
from(select paymenthr,latestdate,codetext,row_number()
over(partition by paymenthr order by paymenthr,latestdate desc)as rowid from cte)T where rowid=1

drop table A
drop table B

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 07:15:03

Thank you ayamas! A bit complex, but it works.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 07:18:27

quote:
Originally posted by Kurmanc

Thank you ayamas! A bit complex, but it works.

You are welcome.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 07:34:30

One last question ayamas,

Is it possible to put the chunk of code in a join, I tried but it did not work?

SELECT *
FROM xxx
INNER JOIN yyy ....
INNER JOIN ( ayamas chunk of code)

It complains on the last row... is it possible to put CTE in a join ?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 07:44:48

Try this.It is without CTE & also without the complex joins in your original query

Create table A(paymentnr varchar(40),infoid int,date datetime)
Create table B(codetext varchar(40),codeid int)

insert into A
select 5000005,1,'2008-03-09'union all
select 5000005,2,'2008-04-10'union all
select 5000006,1,'2008-01-09'

insert into B
select 'a',1 union all
select 'b',2

Select paymentnr,date,codetext from
(select paymentnr,date,codetext,row_number()over(partition by paymentnr order by paymentnr,date desc)
as rowid from
(SELECT paymentnr,date,codetext from A inner join B on a.infoid=B.codeid)
CTE)T
where rowid=1

drop table A
drop table B

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 08:00:41

It works just as fine as the previous one. Well done ayamas!

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 08:07:40

quote:
Originally posted by Kurmanc

One last question ayamas,

Is it possible to put the chunk of code in a join, I tried but it did not work?

SELECT *
FROM xxx
INNER JOIN yyy ....
INNER JOIN ( ayamas chunk of code)

It complains on the last row... is it possible to put CTE in a join ?

As far as using CTE in a join you can have to use like this.

SELECT *
FROM CTE
INNER JOIN yyy ....
INNER JOIN xxx...

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 08:11:54

Thanks for the info,

However with your final shorter query, It worked perfectly (without taking CTE in consideration), thanks!

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-17 : 08:16:15

quote:
Originally posted by Kurmanc

Thanks for the info,

However with your final shorter query, It worked perfectly (without taking CTE in consideration), thanks!

You are welcome again.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-17 : 10:27:58

Hello ayamas,

I had to find my own way of solving it too :)

For your information, this query below works too.


WITH LatestDate ([date]) AS 
(SELECT MAX([date]) as LatestDate
FROM A
INNER JOIN B ON A.infoid=B.codeid
GROUP BY paymentnr)

SELECT paymentnr, codetext
FROM
	A INNER JOIN B ON A.infoid=B.codeid
WHERE A.[date] in (select [date] from LatestDate)
order by paymentnr

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources