MS SQL Question Help

rgmmg

I am trying to group data in a dataset by a date i.e. get data from (MS Sql):

A 400000000 20/05/1994
A 500000000 20/05/1994
A 620000000 01/08/1994
A 695000000 01/09/1994
A 769700000 01/11/1994
B 844700000 01/03/1995
B 944700000 01/04/1995
B 1046000000 13/07/1995
B 1145500000 07/09/1995
B 1246500000 16/11/1995
B 1346500000 01/02/1996


TO
A 769700000 01/11/1994
B 1346500000 01/02/1996


This is the SQL:

************************************************** ***
select t1.symbol,issue.amountissued, MAX(issue.startdate)
from Debt t1
left join Issue t3 on t3.RID = t1.id
left join Market t4 on t4.Id = t3.IssuingMarket
left join issuesize issue on left(issue.symbol,11) = t1.symbol
where (t1.state = 0)
and (t1.descr like '%gov%')

group by t1.symbol,issue.amountissued
order by 2 asc
************************************************** *****
but it doesn't do anything , returning

A 400000000 20/05/1994
A 500000000 20/05/1994
A 620000000 01/08/1994
A 695000000 01/09/1994
A 769700000 01/11/1994
B 844700000 01/03/1995
B 944700000 01/04/1995
B 1046000000 13/07/1995
B 1145500000 07/09/1995
B 1246500000 16/11/1995
B 1346500000 01/02/1996

Any ideas?

seamus

But you're not grouping the data by date. You're grouping it by the first two columns.

What criteria, exactly, do the two rows you have highlighted fulfill that would make them be selected above the others?

bellylint

you are grouping by issue, do you not want to sum it or take the max value?
it is what is causing the multiple

bellylint

ie if you look at


A 400000000
A 500000000
A 620000000
A 695000000
A 769700000

they are all seperate values and cannot be grouped as such

bellylint

seamus wrote: »

But you're not grouping the data by date. You're grouping it by the first two columns.

What criteria, exactly, do the two rows you have highlighted fulfill that would make them be selected above the others?

Its ok with the aggregate function on the date column. It only will pull back the one value after it 'should' group the other items.

rgmmg

seamus wrote: »

But you're not grouping the data by date. You're grouping it by the first two columns.

What criteria, exactly, do the two rows you have highlighted fulfill that would make them be selected above the others?

My understanding is you need to group by non aggregate functions?

rgmmg

bellylint wrote: »

ie if you look at


A 400000000
A 500000000
A 620000000
A 695000000
A 769700000

they are all seperate values and cannot be grouped as such

i want to goup by column 1

bellylint

try this
it will give you
A 01/11/1994
B 01/02/1996

as said your trouble is the issue column cannot be grouped as they are seperate distinct values and you are not applying an aggregate function to compress them into one item


select t1.symbol,MAX(issue.startdate)
from Debt t1
left join Issue t3 on t3.RID = t1.id
left join Market t4 on t4.Id = t3.IssuingMarket
left join issuesize issue on left(issue.symbol,11) = t1.symbol
where (t1.state = 0)
and (t1.descr like '%gov%')

group by t1.symbol
order by 2 asc

rgmmg

bellylint wrote: »

you are grouping by issue, do you not want to sum it or take the max value?
it is what is causing the multiple

If I omit column 2 from the grouping, I get:

"Column 'issue.AmountIssued' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

rgmmg

bellylint wrote: »

try this
it will give you
A 01/11/1994
B 01/02/1996

as said your trouble is the issue column cannot be grouped as they are seperate distinct values and you are not applying an aggregate function to compress them into one item


select t1.symbol,MAX(issue.startdate)
from Debt t1
left join Issue t3 on t3.RID = t1.id
left join Market t4 on t4.Id = t3.IssuingMarket
left join issuesize issue on left(issue.symbol,11) = t1.symbol
where (t1.state = 0)
and (t1.descr like '%gov%')

group by t1.symbol
order by 2 asc

thanks - I can get two columns to work, just not 3

bellylint

rgmmg wrote: »

thanks - I can get two columns to work, just not 3

do you follow why the third column is not working for you?
if you join to that query that I used above you should be able to get out all the details you need.

ie select debt.symbol,issue,date from debt
left join Issue outterIssue on outterIssue.RID = debt.id
left join Market outterMarket on outterMarket .Id = outterIssue.IssuingMarket
left join issuesize outerIssueSize on left(outerIssueSize.symbol,11) = debt.symbol
(select t1.symbol,MAX(issue.startdate) as maxDate
from Debt t1
left join Issue t3 on t3.RID = t1.id
left join Market t4 on t4.Id = t3.IssuingMarket
left join issuesize issue on left(issue.symbol,11) = t1.symbol
where (t1.state = 0)
and (t1.descr like '%gov%') group by t1.symbol) Query

on debt.symbol = Query.symbol and debt.startdate= query.maxdate

Jagera

I don't think you need to join to the Market or Issue tables, as there's nothing used in them. Here's my shot, just done quickly but should give you what you need.

select t1.symbol, i.amountissued, i.startdate
from Debt t1
left join issuesize t2 on t1.symbol=left(t2.symbol,11)
inner join
(
select a.symbol, max(b.startdate) as maxstartdate
from Debt a
left join issuesize b on a.symbol=left(b.symbol,11)
where (a.state = 0)
and (a.descr like '%gov%')
group by a.symbol
) as t on t1.symbol=t.symbol and t2.startdate=t.maxstartdate

You need to consider if there's more than once instance of a date, in which case you'll get multiple rows returned.

rgmmg

Thanks everyone - works now