Select First & Last

[MickDub]

HI all

Quick one

I pulling details from tabel and one the parts is to select the first time and last time in a day that a user writes to the tabel

Example

ID Time User Ext
1 08:00 1 A
2 08:30 1 A
3 08:45 1 A
4 09:00 1 A
5 10:00 1 A

What i need to return are the first tiem and last time
so woudl need

user Start End Ext
1 08:00 10:00 A

Thanks in Advance

Mick

 

[vongrunt]

Looks like standard aggregate query to me:

select [User], Min([Time]) as Start, Max([Time]) as [end], Ext
from myTable
-- where [User] = 1
group by [User], Ext

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[bborissov]

On top of my head

SELECT MyTable.User, 
       Tbl1.Start,
       Tbl2.End,
       Ext
FROM MyTable
INNER JOIN 
(SELECT User, MIN(Time) AS Start FROM MyTable GROUP BY User) Tbl1 ON MyTable.user = Tbl1.User,
INNER JOIN 
(SELECT User, MAX(Time) AS End FROM MyTable GROUP BY User) Tbl2 ON MyTable.user = Tbl2.User
FROM MyTable
GROUP BY MyTable.User, 
         Tbl1.Start,
         Tbl2.End,
         Ext

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

LOL
Why I send such complicate SELECT )))))
Maybe I need more beer.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[MickDub]

cheers lads forgot all about min & max option