Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7857
  • Last Modified:

Pull first record only from each group

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
zorvek (Kevin Jones)
Asked:
zorvek (Kevin Jones)
3 Solutions
 
RaisinJCommented:
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
 
BulZeyECommented:
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
 
ram2098Commented:
I guess....you can do it this way too...

select f1, f2, f3
from table1
where f2 = (select min(f2) from table1)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
BulZeyECommented:
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
 
zorvek (Kevin Jones)ConsultantAuthor Commented:
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
 
ram2098Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now