Most recent record, when multiple?

[hblabonte]

I'm trying to create a query that will return the top value when multiple rows are retrieved for one person.

For example, I have a person table. A person can be entered multiple times depending on the type (employee, former employee, retired, part time, faculty, former faculty).

Select id, type
from people
where type = 'employee' or
type = 'former employee' or
type = 'faculty' or
type = 'retired'

Sample output...
ID: Type:
1 employee
2 faculty
2 former employee
3 retired

What I need is to display only the top record if there are multiple rows per ID. I can not use select distinct, as that limits me to only pulling the ID number. I need to display the type field.

The output I'm looking to capture:
1 employee
2 faculty
3 retired

Any help would be appreciated!
-Heather

 

[IFELSETHEN]

Hi,
I notice that you can save some code if you use the IN Operator, like this ....
*******************************
Select id, type
from people
where type IN ('employee','former employee','faculty','retired')
*******************************
... on to your question. Do you have a date field that gets filled in while creating the new record? If yes then Order by that field.



*********************
John Nyhart

http://www.nyhart.com

*********************

 

[Tranman]

Heather,
This should do the trick:

SELECT PEOPLE.ID, Min(PEOPLE.TYPE) AS MinOfTYPE
FROM PEOPLE
GROUP BY PEOPLE.ID;

Luck,
Tranman

 

[PGTurner]

It would seem to me that Tranman's would give you the alphanumeric minimum, not necessarily the first - in this case it works because "faculty" is less than "former employee".

Maybe it isn't possible to be a "former employee" first and then a "faculty" but unless all the types happen to align correctly it could return incorrect result.

You could use First (or Last).

SELECT People.ID, First(People.Type) AS FirstOfType
FROM People
GROUP BY People.ID

On the surface it looks like it might meet your needs,
but I don't think it is a good idea to depend only on the order they were entered to pick the first record without an additional field - a date-time stamp or perhaps an autonumber.

MS help for first/last points out:
"Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary."

In my opinion, if order is important, you need an ORDER BY clause on a real or calculated field

 

[hblabonte]

Thanks for all of the suggestions. There is a "revision_date" column which I can use. I should have specified that I am using SQL Server 7.0. The First/last functions are not recognized with query analyzer. That would have been a nice idea.

Here's what I have done.

select peopletype.people_code_id,
--people_type,
max(revision_date)
from peopletype
where .....
group by peopletype.people_code_id

This returns one row, which is correct the row that I want:

people_code_id
-------------- ---------------------------
P900000001 2003-01-03 00:00:00.000


When I try to print the people_type colum, which I need, I get two rows...

select people_type,
peopletype.people_code_id,
max(revision_date)
from peopletype
where .....
group by peopletype.people_code_id,
people_type

This returns:
people_type people_code_id
----------- -------------- ---------------------------
EMPP P900000001 2003-01-03 00:00:00.000
FACP P900000001 2002-07-25 00:00:00.000

How can I get this to return only the top row??

 

[Tranman]

It looks like you need to create another query that joins the output of the first query back to your peopletype table with the clause:

&quot;where <query_id>.people_code_id = peopletype.people_code_id AND <query_id>.revision_date = peopletype.revision_date;

All of the desired fields in the peopletype table should be available to you for the selected person in this query.

Tranman

 

[hblabonte]

Great idea Tranman! I tried it by specifying an ID with a subquery and got the results that I need. That

select peopletype.people_code_id, peopletype.people_type
from peopletype
where peopletype.people_code_id = 'P900000001' AND
revision_date =
(select --peopletype.people_code_id,
max(revision_date) as Mdate
from peopletype
where peopletype.people_code_id ='P900000001' AND ...
)

group by peopletype.people_code_id, people_type

I've never referenced an outside query, so bear with me. If I create the subquery as a view (SQL Server), would I reference it as follows?

select peopletype.people_code_id, peopletype.people_type
from peopletype
where <VIEW>.people_code_id = peopletype.people_code_id AND <VIEW>.revision_date = people.revision_date

Thanks for that tip!!

 

[Tranman]

Not exactly certain about SQL-I'm an Oracle guy. What you said would work in Oracle.

You could also accomplish the same thing with an imbedded query, by aliasing the field names.

Luck,
Tranman

BTW, have you considered the SQL forum?

 

[hblabonte]

Thanks for the tips... success!!!!