Showing record rows in group by SQL

[laurenq uantrell]

I know I should know this but here goes...

I have a table with the following rows:
UniqueID int IDENTITY
ParentUniqueID int
RecordLabel nvarchar(50)
RecordText ntext
RecordDate DateTime

What I'm trying to do is to group all records by ParentUniqueID
and display the RecordLabel, RecordText and RecordDate from the most
recent row for each grouped ParentUniqueID.

In other words I need to GroupBy ParentUniqueID and show only the
RecordLabel, RecordText and RecordDate for the MAX of RecordDate:

Records in Table:
UniqueID ParentUniqueID Recordlabel RecordText RecordDate
1 1 ThreadOne Blah-Abc 1/1/2005
2 1 ThreadOne Blah-Def 1/2/2005
3 3 ThreadTwo Blah-Ghi 1/2/3005
4 3 ThreadTwo Blah-Jkl 1/3/2005
5 1 ThreadOne Blah-Mno 1/4/2005
6 6 ThreadThree Blah-Pqr 1/5/2005

What I want to return:
UniqueID ParentUniqueID Recordlabel RecordText RecordDate
4 3 ThreadTwo Blah-Jkl 1/3/2005
5 1 ThreadOne Blah-Mno 1/4/2005
6 6 ThreadThree Blah-Pqr 1/5/2005

The problem is I can't use a groupby clause with RecordLabel,
RecordText, RecordDate because then it just returns all records (plus
Recordtext is an ntext and I have to deal with that issue as well...)
Any help is appreciated!
lq

[Hugo Kornelis]

On 22 Jan 2005 14:36:33 -0800, laurenq uantrell wrote:

I know I should know this but here goes...

I have a table with the following rows:
UniqueID int IDENTITY
ParentUniqueID int
RecordLabel nvarchar(50)
RecordText ntext
RecordDate DateTime

What I'm trying to do is to group all records by ParentUniqueID
and display the RecordLabel, RecordText and RecordDate from the most
recent row for each grouped ParentUniqueID.

In other words I need to GroupBy ParentUniqueID and show only the
RecordLabel, RecordText and RecordDate for the MAX of RecordDate:

(snip)

Hi lauren,

Try if this works:

SELECT a.UniqueID, a.ParentUniqueID,
a.Recordlabel, a.RecordText, a.RecordDate
FROM MyTable AS a
WHERE NOT EXSTS
(SELECT *
FROM MyTable AS b
WHERE b.ParentUniqueID = a.ParentUniqueID
AND b.RecordDate > a.RecordDate)

Or, as an alternative:

SELECT a.UniqueID, a.ParentUniqueID,
a.Recordlabel, a.RecordText, a.RecordDate
FROM MyTable AS a
LEFT JOIN MyTable AS a
ON b.ParentUniqueID = a.ParentUniqueID
AND b.RecordDate > a.RecordDate
WHERE b.UniqueID IS NULL

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Lee Tudor]

This should do the job nicely...

SELECT * FROM table1
JOIN (
SELECT ParentUniqueID, Max(RecordDate) RecordDate
FROM Table1
GROUP BY ParentUniqueID
) Sub
ON Sub.ParentUniqueID = table1.ParentUniqueID
AND Sub.RecordDate = table1.RecordDate

Mr Tea
http://mr-tea.blogspot.com

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I know I should know this but here goes...

I have a table with the following rows:
UniqueID int IDENTITY
ParentUniqueID int
RecordLabel nvarchar(50)
RecordText ntext
RecordDate DateTime

What I'm trying to do is to group all records by ParentUniqueID
and display the RecordLabel, RecordText and RecordDate from the most
recent row for each grouped ParentUniqueID.

In other words I need to GroupBy ParentUniqueID and show only the
RecordLabel, RecordText and RecordDate for the MAX of RecordDate:

Records in Table:
UniqueID ParentUniqueID Recordlabel RecordText RecordDate
1 1 ThreadOne Blah-Abc 1/1/2005
2 1 ThreadOne Blah-Def 1/2/2005
3 3 ThreadTwo Blah-Ghi 1/2/3005
4 3 ThreadTwo Blah-Jkl 1/3/2005
5 1 ThreadOne Blah-Mno 1/4/2005
6 6 ThreadThree Blah-Pqr 1/5/2005

What I want to return:
UniqueID ParentUniqueID Recordlabel RecordText RecordDate
4 3 ThreadTwo Blah-Jkl 1/3/2005
5 1 ThreadOne Blah-Mno 1/4/2005
6 6 ThreadThree Blah-Pqr 1/5/2005

The problem is I can't use a groupby clause with RecordLabel,
RecordText, RecordDate because then it just returns all records (plus
Recordtext is an ntext and I have to deal with that issue as well...)
Any help is appreciated!
lq

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

What I'm trying to do is to group all records by ParentUniqueID
and display the RecordLabel, RecordText and RecordDate from the most
recent row for each grouped ParentUniqueID.

In other words I need to GroupBy ParentUniqueID and show only the
RecordLabel, RecordText and RecordDate for the MAX of RecordDate:

One more variation to att to Hugo's suggestions:

SELECT a.RecordLabel, a.RecordText, a.RecordDate
FROM tbl a
JOIN (SELECT ParentUniqueID, maxdate = MAX(RecordDate)
FROM tbl
GROUP BY ParentUniqueID) AS b
ON a.ParentUniqueID = b.ParentUniqueID
AND a.RecordDate = b.maxdate

Note that if there are more that one row for the latest RecordDate,
you will get two rows in the output, but that is per the spec you've
given us.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Lee Tudor]

Q. When is a join not a join
A. When its a join to an aggregated self view on the group condition and
aggregate

performance wise, this version is far superior to the alternates due to the
query optimiser specifically checking for this type of query and having a
special way of dealing with it. Its a shame its not this clever across the
board. :)

Mr Tea
http://mr-tea.blogspot.com

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn********************@127.0.0.1...

laurenq uantrell (la*************@hotmail.com) writes:

What I'm trying to do is to group all records by ParentUniqueID
and display the RecordLabel, RecordText and RecordDate from the most
recent row for each grouped ParentUniqueID.

In other words I need to GroupBy ParentUniqueID and show only the
RecordLabel, RecordText and RecordDate for the MAX of RecordDate:

One more variation to att to Hugo's suggestions:

SELECT a.RecordLabel, a.RecordText, a.RecordDate
FROM tbl a
JOIN (SELECT ParentUniqueID, maxdate = MAX(RecordDate)
FROM tbl
GROUP BY ParentUniqueID) AS b
ON a.ParentUniqueID = b.ParentUniqueID
AND a.RecordDate = b.maxdate

Note that if there are more that one row for the latest RecordDate,
you will get two rows in the output, but that is per the spec you've
given us.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[laurenq uantrell]

Erland,
Thanks for the response and the SQL. I know it had to be a subquery but
wasn't lucid enough to figure it out.
I am actually using thr MAX of ParentUniqueID not the datetime column -
I just set it up that way to make it easier to show what I was trying
to do, so I won't get duplicate rows.
What I need to fugure out know is how to sort it with an ORDER BY
clause...
Thanks again!
lq

[laurenq uantrell]

I'm not sure which version you're telling me to use here...

[Lee Tudor]

erkland's (23:04)/my (22:58) suggestion (they are the same) offers the best
performance of the 3 different ones here,

Mr Tea

, "laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

I'm not sure which version you're telling me to use here...