June 10, 2008 at 5:15 am
I have a tricky (for me) SQL issue that I hope I could get some advice on:
Table ClientUnitBalance contains: ClientId, UnitBalance, Date with a record for each client for each day. I would like to extract the most recent UnitBalance for each client.
Using MAX(Date), requires me to include UnitBalance in the GROUP BY, meaining that I get multiple rows for each client, for each unique UnitBalance.
I've also tried several other methods. Surely someone has run into this before?
How would you write the SQL for this?
Thanks in advance.
June 10, 2008 at 5:41 am
You can use a subquery if you want
Select ClientId
Unitbalance,
Date
from Table
where Date = (select z.max Date
from Table_2 z
where z.clientId = Table.ClientId)
June 10, 2008 at 5:58 am
Try the following - that the result you want?
begin tran
create table a (clientid int,
Unitbalance numeric(21,0),
Dates datetime)
insert into a values (123, 999, '1 jan 2008 12:00:01:001')
insert into a values (456, 888, '1 jan 2008 12:00:01:006')
insert into a values (456, 777, '1 jan 2008 12:00:01:007')
insert into a values (789, 222, '1 jan 2008 12:00:01:008')
insert into a values (789, 333, '1 jan 2008 12:00:01:009')
insert into a values (123, 999, '1 jan 2008 12:00:01:010')
insert into a values (123, 111, '1 jan 2008 12:00:01:011')
select clientid,
unitbalance,
dates
from a
where a.dates = (select max(bb.dates)
from a bb
where bb.clientid = a.clientid)
rollback tran
June 10, 2008 at 7:09 am
The subquery allows you to find the date you want, the correlation does it for each client.
It's the way I'd do it as well.
June 11, 2008 at 12:28 am
I think subquery is too slow.
You could try something like this.
begin tran
create table a (clientid int, Unitbalance numeric(21,0),Dates datetime)
insert into a values (123, 999, '20080101')
insert into a values (456, 888, '20080102')
insert into a values (456, 777, '20080103')
insert into a values (789, 222, '20080104')
insert into a values (789, 333, '20080105')
insert into a values (123, 999, '20080106')
insert into a values (123, 111, '20080107')
SELECT * FROM a
SELECT ClientId, MaxDate = MAX(dates)
INTO #xx
FROM a
GROUP BY ClientId
SELECT a.* FROM a JOIN #xx x ON (x.MaxDate = a.Dates AND a.ClientId = x.ClientId)
rollback tran
June 11, 2008 at 3:08 pm
Why use a temp table and incur the overhead??
create table a (clientid int, Unitbalance numeric(21,0),Dates datetime)
set nocount on
insert into a values (123, 999, '20080101')
insert into a values (456, 888, '20080102')
insert into a values (456, 777, '20080103')
insert into a values (789, 222, '20080104')
insert into a values (789, 333, '20080105')
insert into a values (123, 999, '20080106')
insert into a values (123, 111, '20080107')
SELECT * FROM a
SELECT a.* FROM a
JOIN (
SELECT ClientId, MaxDate = MAX(dates)
FROM a
GROUP BY ClientId
) x ON (x.MaxDate = a.Dates AND a.ClientId = x.ClientId)
drop table a
Depening on the # of rows in your table and the indexing, this query and the sub-query version should be checked against each other for performance. This type of query usually wins out.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply