How to choose the first record based on time

Question

I have two tables and call table and a after call work table. These two tables have a one to one relationship even tho the after call work records are stored in the call table. You can link a call to a after call work call by joining on three values in the call table. The call table also holds the values of the start and end time of the call.

The data in the after call work table is a total mess there are occasions where one call has many after call work records. My client wants me to pick out the first record based on the start time of the call and only take this 1 row of data.

Its been suggested to use RANKING function but I'm unfamiliar with this any body got any ideas?

If anything needs explaining further let me know.

Thank you

score 0 · Answer 1 · answered Mar 25 '13 at 17:00

Self extracting ranking and dense ranking example ASSUMING you are using SQL Server 2008 or newer.

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad'),('Michael'),('Ray'),('Erik'),('Queyn');

declare @Orders table ( OrderID int identity, PersonID int, Desciption varchar(32), Amount int);

insert into @Orders values (1, 'Shirt', 20),(1, 'Shoes', 50),(2, 'Shirt', 22),(2, 'Shoes', 20),(3, 'Shirt', 20),(3, 'Shoes', 50),(3, 'Hat', 20),(4, 'Shirt', 20),(5, 'Shirt', 20),(5, 'Pants', 30),
(6, 'Shirt', 20),(6, 'RunningShoes', 70),(7, 'Shirt', 22),(7, 'Shoes', 40),(7, 'Coat', 80);

with a as 
    (
    Select
        person
    ,   o.Desciption
    ,   o.Amount
    ,   rank() over(partition by p.personId order by Amount) as Ranking
    ,   Dense_rank() over(partition by p.personId order by Amount) as DenseRanking
    from @Person p
        join @Orders o on p.personID = o.PersonID
    )
select *
from a 
where Ranking <= 2  -- determine top 2, 3, etc.... whatever you want.
order by person, amount

How to choose the first record based on time

1 Answers1