UKC

This is probably so easy I’m missing it.

Got this simple table. I want to pull back a single record for each of the four Customers showing their most recent appointment.

AppID CustID Name Date Value
1 1 Paul 01/01/2012 100
2 1 Paul 01/05/2012 200
3 1 Paul 01/01/2013 50
4 2 John 04/05/2012 100
5 2 John 05/08/2012 200
6 2 John 05/10/2012 300
7 3 George 02/03/2010 45
8 3 George 01/10/2012 45
9 3 George 01/02/2013 10
10 4 Ringo 05/06/2012 75
11 4 Ringo 05/07/2012 75
12 4 Ringo 01/11/2012 2

If it was only the AppID, CustID, Name and Date I could just aggregate them – GROUP the first three and MAX the date, the Value column throws a spanner in the works (and this is just a dumbed down example…my tables a lot more involved than this)

This is what I would want the output to be.

AppID CustID Name Date Value
3 1 Paul 01/01/2013 50
6 2 John 05/10/2012 300
9 3 George 01/02/2013 10
12 4 Ringo 01/11/2012 2

Is it doable?

In reply to ThunderCat:

That didn't paste in as well as I hoped...

In reply to ThunderCat: thought about posting that on a SQL forum?

In reply to ThunderCat: If you're using SQL Server this will do the trick.


;WITH x AS
(
SELECT AppID, CustID, Name, [Date], Value, RANK() OVER(PARTITION BY CustID ORDER BY [Date] DESC) Ranking
FROM TableName
)

SELECT AppID, CustID, Name, [Date], Value
FROM x
WHERE Ranking = 1

In reply to Parrys_apprentice:

Yup, but I've seen SQL questions get answered a lot quicker on here.

Hope that's ok,

In reply to ThunderCat:

SELECT * FROM customers AS c1 WHERE c1.Date=(SELECT MAX(c2.Date) FROM customers AS c2 WHERE c2.CustId=c1.CustId);

If it's a big tabe there me some overhead to the nested SELECT.

If there are multiple records for a particular name on the same MAX(Date) these will show.

In reply to interdit:
I don't think that'll work as you are only joining into your subselect on date.

In reply to ThunderCat: Not sure if we're talking about Oracle or Microsoft, but if Oracle:
select *
from SimpleTable
where
(
custid,
date1
) in
(
select
custid,
max(date1) date1
from SimpleTable
group by
custid
);

date is a reserved word in Oracle, so have used date1.

In reply to ThunderCat:
I just thought wouldn't it be great if UKC had an SQL playground where we could try out these things.
Lo and behold I found one at http://sqlfiddle.com/ <-- it's brill!

Pasting the raw data into "Text to DLL" tool
AppID CustID Name Date Value
1 1 Paul 01/01/2012 100
2 1 Paul 01/05/2012 200
3 1 Paul 01/01/2013 50
4 2 John 04/05/2012 100
5 2 John 05/08/2012 200
6 2 John 05/10/2012 300
7 3 George 02/03/2010 45
8 3 George 01/10/2012 45
9 3 George 01/02/2013 10
10 4 Ringo 05/06/2012 75
11 4 Ringo 05/07/2012 75
12 4 Ringo 01/11/2012 2

I got

CREATE TABLE Table1
(`AppID` int, `CustID` int, `Name` varchar(6), `Date` datetime, `Value` int)
;

INSERT INTO Table1
(`AppID`, `CustID`, `Name`, `Date`, `Value`)
VALUES
(1, 1, 'Paul', '2012-01-01 00:00:00', 100),
(2, 1, 'Paul', '2012-01-05 00:00:00', 200),
(3, 1, 'Paul', '2013-01-01 00:00:00', 50),
(4, 2, 'John', '2012-04-05 01:00:00', 100),
(5, 2, 'John', '2012-05-08 01:00:00', 200),
(6, 2, 'John', '2012-05-10 01:00:00', 300),
(7, 3, 'George', '2010-02-03 00:00:00', 45),
(8, 3, 'George', '2012-01-10 00:00:00', 45),
(9, 3, 'George', '2013-01-02 00:00:00', 10),
(10, 4, 'Ringo', '2012-05-06 01:00:00', 75),
(11, 4, 'Ringo', '2012-05-07 01:00:00', 75),
(12, 4, 'Ringo', '2012-01-11 00:00:00', 2)
;

I could then "Build Schema" and run the query on the table

SELECT MAX(AppID), CustID, Name, MAX(Date), Value FROM Table1 GROUP BY CustID

to obtain
MAX(APPID) CUSTID NAME MAX(DATE) VALUE
3 1 Paul January, 01 2013 00:00:00+0000 100
6 2 John May, 10 2012 01:00:00+0000 100
9 3 George January, 02 2013 00:00:00+0000 45
12 4 Ringo May, 07 2012 01:00:00+0000 75

I suspect my query relies on AppID being an autoincrement PK so MAX(DATE) corresponds to MAX(AppID) for that CustID.


In conclusion, my query may be wrong but http://sqlfiddle.com/ is brilliant.

In reply to interdit:

This seems to work fine!

Nice one - really appreciate that!

In reply to ThunderCat: fwiw my example code is set-based and will scale, the examples with correlated sub-queries will develop performance issues with a larger dataset. =o)

In reply to highaltitudebarista:

It's quite a small one at the moment.

I'm still trying to understand exactly HOW it's working to be honest...