top of group

27 Jul 2006 8:39 PM

vincent

hello,

I have a detail table that contains 100000 records. The account code field
contains an account#. The date field contains various dates that transactions
occurred against my account.
Iâ€™ve used the order by clause to sort by account_code and date which has
given me.

Account_code        date         balance
119111            20060727        1000.78
119111            20060726        6718.12
119111            20060725        1237.78
115789            20060727        2435.89
115789            20060726        1245.65
115789            20060725        1877.50

I need to display one the top record of each group of my record set. In
other words.
119111            20060727        1000.78
115789            20060727        2435.89

thank you in advance.

27 Jul 2006 9:07 PM

Hugo Kornelis

On Thu, 27 Jul 2006 13:39:01 -0700, vincent wrote:

Show quote

>hello,
>
>I have a detail table that contains 100000 records. The account code field
>contains an account#. The date field contains various dates that transactions
>occurred against my account.
>I’ve used the order by clause to sort by account_code and date which has
>given me.
>
>Account_code        date         balance
>119111            20060727        1000.78
>119111            20060726        6718.12
>119111            20060725        1237.78
>115789            20060727        2435.89
>115789            20060726        1245.65
>115789            20060725        1877.50
>
>I need to display one the top record of each group of my record set. In
>other words.
>119111            20060727        1000.78
>115789            20060727        2435.89
>
>thank you in advance.

Hi Vincent,

Here is one possible way:

SELECT a.Account_code, a.date, a.balance
FROM   YourTable AS a
WHERE a.date = (SELECT MAX(b.date)
                 FROM   YourTable AS b
                 WHERE b.Account_code = a.Account_code);

And here's another way - less intuitive but often better performing:

SELECT      a.Account_code, a.date, a.balance
FROM        YourTable AS a
INNER JOIN (SELECT   Account_code, MAX(date)
            FROM     YourTable
            GROUP BY Accoount_code) AS b
      ON    b.Account_code = a.Account_code;

(Untested - see www.aspfaq.com/50006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP