Solved

Pull first record only from each group

Posted on 2004-10-12
6
7,826 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

840 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