Solved

Pull first record only from each group

Posted on 2004-10-12
6
7,811 Views
Last Modified: 2008-01-09
I have a single table T with three fields F1, F2, and F3. I want to group by F1, sort by F2, and select the first occurence of F1, F2, and F3. So if my table looks like:

F1 F2 F3
01 01 A
01 02 B
02 01 D
02 02 C

I want my resulting recordset to look like:

F1 F2 F3
01 01 A
02 01 D

If it wasn't for F3 I could use the MIN function to pull the lowest F2 value and be done. There does not seem to be a FIRST aggregate function in SQL Server like there is in Access.

Kevin
0
Comment
Question by:zorvek (Kevin Jones)
6 Comments
 
LVL 6

Assisted Solution

by:RaisinJ
RaisinJ earned 150 total points
ID: 12294169
Select
    T.F1,
    MIN(T.F2) As F2
Into #TempTableA
From
    @YourTable T
Group By
    T.F1

Select
    tA.F1,
    tA.F2,
    T.F3
From
    #TempTableA tA
    INNER JOIN @YourTable T ON
        tA.F1 = T.F1 AND
        tA.F2 = T.F2
Order By
    1,
    2

Drop Table #TempTableA

... The above syntax is really simple.  You could probably write a smaller query (little more complex) but, the above wil work just fine.  Basically, it gets your F1 and F2 values into a temp table grouped the way you want them.  You then just simply go back to your original table and pull out the associated F3 field to the temp table's F1 and F2 fields...
0
 
LVL 4

Assisted Solution

by:BulZeyE
BulZeyE earned 150 total points
ID: 12294374
Or you could do it in just one statement like

SELECT t1.F1,
 t1.f2,
t1.f3
from <yourtable> t1
inner join (select f1,min(f2) as f2
                from <yourtable>
                group by f1) t2
 on t1.f1 = t2.f1
  and t1.f2 = t2.f2
order by t1.f1,t1.f2
0
 
LVL 11

Accepted Solution

by:
ram2098 earned 200 total points
ID: 12295648
I guess....you can do it this way too...

select f1, f2, f3
from table1
where f2 = (select min(f2) from table1)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 4

Expert Comment

by:BulZeyE
ID: 12298534
you could do that but that would assume that there are the same values for f2 for every value of f1 which may not be the case.  The question was to get the first occurance of f2 for each f1....
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 12299332
ram2098's solution was pretty close to what I actually got working:

   SELECT F1, F2, F3
   FROM T AS T1
   WHERE (F2 = (SELECT MIN(F2) FROM T AS T2 WHERE T2.F1 = T1.F1))

So, it looks like RaisinJ's and BulZeyE's would actually work but I like the simplicty of ram2098's approach which is just missing a part...

Thanks!

Kevin
0
 
LVL 11

Expert Comment

by:ram2098
ID: 12304838
I know I might be overlooking something by looking at your solutions..Bullzeye, thanks for the clarification.

Any way, good to see my solution helped :)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now