pull first record for each

[simian336]

I have a list ordered by Account then branch.

Account Branch
0000000605
0000000605 1
0000000605 10
0000000606
0000000606 3
0000000607 2
0000000608 2
0000000608 5


How would I pull only the first account wheather or not the branch is blank.

desired result would be

0000000605
0000000606
0000000607 2
0000000608 2

Thanks Simi

 

[RiverGuy]

SELECT Account, MIN(Branch) AS Branch
FROM YourTable
GROUP BY Account
ORDER BY Account

 

[simian336]

easy enough... I should have thought of it myeself.

Thanks Riverguy

Simi

 

[djj55]

Watch out for NULL

[tt]Warning: Null value is eliminated by an aggregate or other SET operation.[/tt]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

[tt]Warning: Null value is eliminated by an aggregate or other SET operation.[/tt]

This can be "fixed" be setting ANSI_WARNINGS off. Ex:

Set ANSI_WARNINGS Off

SELECT Account, MIN(Branch) AS Branch
FROM YourTable
GROUP BY Account
ORDER BY Account

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Just wanted to show that if Branch was null it would not show in the aggregate, see "0000000605" which would return 1. At least that is how my test worked.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[hopper44]

If you are using SQL Server 2005 or 2008, then you can easily do this by using the RANK function as shown below:

SELECT tmp.Account, tmp.Branch FROM
(SELECT Account, Branch, RANK() OVER (Partition by Account ORDER BY Branch) as Rank
FROM AccountBranch
) tmp
WHERE tmp.Rank = 1

Explanation:
************
The inner derived table ranks the records broken by Account i.e.

Account Branch Rank
********* ****** ****
0000000605 NULL 1
0000000605 1 2
0000000605 10 3
0000000606 NULL 1
0000000606 3 2
0000000607 2 1
0000000607 5 2
0000000608 2 1

and the outer select has a WHERE clause to filter only records that have a Rank of 1.

Simple yet powerful.