Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Pull first record only from each group

Posted on 2004-10-12
6
Medium Priority
?
7,851 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 600 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 600 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 800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

571 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