fiore45
asked on
Sql query
Hi,
I need to write a query that gives me following results.
Table :
Query should give last records of each EDUID value.
Expected Result:
Thanks.
I need to write a query that gives me following results.
Table :
EDUID START_TIME DTMF
4e418e4f 2011-08-09 22:48:53.827 2164724036
4e418e55 2011-08-09 22:51:44.687 2125539806
4e419196 2011-08-09 22:58:36.950 2125354614
4e419181 2011-08-09 22:59:08.697 2
4e419188 2011-08-09 22:59:16.347 4642260017
4e419181 2011-08-09 22:59:28.597 nmt
4e419196 2011-08-09 22:59:30.053 2164423695
4e41919d 2011-08-09 22:59:36.387 Disc
4e419198 2011-08-09 22:59:31.683 nmt
4e418e4f 2011-08-09 22:59:30.530 224243
4e419188 2011-08-09 22:59:39.347 4642265000Query should give last records of each EDUID value.
Expected Result:
EDUID START_TIME DTMF
4e418e4f 2011-08-09 22:59:30.530 224243
4e419181 2011-08-09 22:59:28.597 nmt
4e419196 2011-08-09 22:59:30.053 2164423695
4e418e55 2011-08-09 22:51:44.687 2125539806
4e419188 2011-08-09 22:59:39.347 4642265000
4e41919d 2011-08-09 22:59:36.387 Disc
4e419198 2011-08-09 22:59:31.683 nmtThanks.
why dont you use order by asc or desc which ever way you want for starttime or eduid ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select * from Test1 where START_TIME in (select MAX(start_time) start_time from test1 group by eduid)
@sachinpatil10d
the subquery would need to be corelated otherwise multiple rows per eduid could be present....
the subquery would need to be corelated otherwise multiple rows per eduid could be present....
@Lowfatspread
multiple rows case would be if the start_time is same for same eduid
so can i think this query should work
select distinct * from Test1 where START_TIME in (select MAX(start_time) start_time from test1 group by eduid)
multiple rows case would be if the start_time is same for same eduid
so can i think this query should work
select distinct * from Test1 where START_TIME in (select MAX(start_time) start_time from test1 group by eduid)
FROM SomeTable t1 INNER JOIN
(SELECT t2.EDUID, MAX(t2.START_TIME) AS START_TIME
FROM SomeTable t2
GROUP BY t2.EDUID) x ON t1.EDUID = x.EDUID AND t1.START_TIME = x.START_TIME