Grouping one column and First value in another???

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!

I´m facing a query which I in the beginning didn´t thought should be so
difficult to produce, and maby it isn´t. It´s just that I can´t figure out
how to do!

I have a query in which I have [FamilyID] and [PersonID]. Both fields are
sourted ascending and I´d like to get the first [PersonID] of all familys.
Her´s what it look´s like:

FamilyID...PersonID
2...............637
2..............640
2.............1055
4.............34
4............173
6............338
6............508

And this is what I´d like to get out of this:
FamilyID...PersonID
2...............637
4..............34
6..............338

All fields are in ONE table named <tblFamilyPersons>

TIA!
// Niklas
 
Niklas said:
Hi!

I´m facing a query which I in the beginning didn´t thought should be so
difficult to produce, and maby it isn´t. It´s just that I can´t figure out
how to do!

I have a query in which I have [FamilyID] and [PersonID]. Both fields are
sourted ascending and I´d like to get the first [PersonID] of all familys.
Her´s what it look´s like:

FamilyID...PersonID
2...............637
2..............640
2.............1055
4.............34
4............173
6............338
6............508

And this is what I´d like to get out of this:
FamilyID...PersonID
2...............637
4..............34
6..............338

All fields are in ONE table named <tblFamilyPersons>
Click to expand...

SELECT
f.FamilyID
, (SELECT TOP 1 PersonID FROM tblFamilyPerson WHERE f.FamilyID =
FamilyID ORDER BY PersonID)
FROM
tblFamilyPersons f
GROUP BY
f.FamilyID
 
Thank´s a lot John!

I realy appreciate your quick help!

// Niklas


John Mishefske said:
Niklas said:
Hi!

I´m facing a query which I in the beginning didn´t thought should be so
difficult to produce, and maby it isn´t. It´s just that I can´t figure
out how to do!

I have a query in which I have [FamilyID] and [PersonID]. Both fields are
sourted ascending and I´d like to get the first [PersonID] of all
familys. Her´s what it look´s like:

FamilyID...PersonID
2...............637
2..............640
2.............1055
4.............34
4............173
6............338
6............508

And this is what I´d like to get out of this:
FamilyID...PersonID
2...............637
4..............34
6..............338

All fields are in ONE table named <tblFamilyPersons>
Click to expand...

SELECT
f.FamilyID
, (SELECT TOP 1 PersonID FROM tblFamilyPerson WHERE f.FamilyID = FamilyID
ORDER BY PersonID)
FROM
tblFamilyPersons f
GROUP BY
f.FamilyID
Click to expand...
 
Why not just use the following?

SELECT F.FamilyID, Min(PersonId) as PID
FROM tblFamilyPersons as F
GROUP BY f.FamilyID

That seems simpler to code and may run faster than using a coordinated sub-query
in the Select clause.

John said:
Hi!

I´m facing a query which I in the beginning didn´t thought should be so
difficult to produce, and maby it isn´t. It´s just that I can´t figure out
how to do!

I have a query in which I have [FamilyID] and [PersonID]. Both fields are
sourted ascending and I´d like to get the first [PersonID] of all familys.
Her´s what it look´s like:

FamilyID...PersonID
2...............637
2..............640
2.............1055
4.............34
4............173
6............338
6............508

And this is what I´d like to get out of this:
FamilyID...PersonID
2...............637
4..............34
6..............338

All fields are in ONE table named <tblFamilyPersons>
Click to expand...

SELECT
f.FamilyID
, (SELECT TOP 1 PersonID FROM tblFamilyPerson WHERE f.FamilyID =
FamilyID ORDER BY PersonID)
FROM
tblFamilyPersons f
GROUP BY
f.FamilyID
Click to expand...
 
Ho John!

I´ll, just for the fun of it, try this one out as well! Because I´d like to
learn more and this is a good opportunity to do so.

The thing though with speed isn´t relevant in my case because what I´m
trying to do here is just to prepere myself for movinge data over from an
old db that I wrote 1999 to a new one which I´m working on right now. So
this Q is only going to be run ONCE and never more. So I guess I can wait a
couple of ms more ;-)

Thank´s anyway for sharing your knowledge with me!

// Niklas


John Spencer (MVP) said:
Why not just use the following?

SELECT F.FamilyID, Min(PersonId) as PID
FROM tblFamilyPersons as F
GROUP BY f.FamilyID

That seems simpler to code and may run faster than using a coordinated
sub-query
in the Select clause.

John said:
Hi!

I´m facing a query which I in the beginning didn´t thought should be so
difficult to produce, and maby it isn´t. It´s just that I can´t figure
out
how to do!

I have a query in which I have [FamilyID] and [PersonID]. Both fields
are
sourted ascending and I´d like to get the first [PersonID] of all
familys.
Her´s what it look´s like:

FamilyID...PersonID
2...............637
2..............640
2.............1055
4.............34
4............173
6............338
6............508

And this is what I´d like to get out of this:
FamilyID...PersonID
2...............637
4..............34
6..............338

All fields are in ONE table named <tblFamilyPersons>
Click to expand...

SELECT
f.FamilyID
, (SELECT TOP 1 PersonID FROM tblFamilyPerson WHERE f.FamilyID =
FamilyID ORDER BY PersonID)
FROM
tblFamilyPersons f
GROUP BY
f.FamilyID
Click to expand...
Click to expand...
 
Back
Top