Solved

Pull first record only from each group

Posted on 2004-10-12
6
7,821 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 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