Max row set. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Max row set.

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-13 : 03:38:31

Hi i've a logins table where it keeps track of a login number and the employees who use them, i want a query that only shows the last employee who was the owner of that user id. I've a date added column which would give me the latest date added so i can sort by this for a employee but i want to only show the max date added in all cases. any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 04:09:37

Can you provide a table layout, some sample data and an example of what to expect from the output query?

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-13 : 04:18:24

So you want:

select l.login, max(l.dateadded) maxdate
from logins l
group by l.login

--but with the other details from the logins table that correspond to these rows as well?

So...

select l2.*
from
(select l.login, max(l.dateadded) maxdate from logins l group by l.login) l1
join
logins l2
on l2.login = l1.login
and l2.dateadded = l1.maxdate

You could probably do it with an OVER clause too, but it would be less intuitive, and it would take longer for me to work it out. ;)

Rob Farley
http://robfarley.blogspot.com

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-13 : 04:29:59

Here is my query, am trying to work on, can you have a look at it.

SELECT TOP 100 PERCENT dbo.tbl_Voy_Employee.EmpLastName, dbo.tbl_Voy_Employee.EmpFirstname, dbo.tbl_Voy_Employee.EmployeeID,
dbo.tbl_login_ids_voy.empl_id, dbo.tbl_login_ids_voy.date_added, dbo.tbl_login_ids_voy.user_id
FROM dbo.tbl_login_ids_voy INNER JOIN
dbo.tbl_Voy_Employee ON dbo.tbl_login_ids_voy.user_id = dbo.tbl_Voy_Employee.SPTelsetID
WHERE (dbo.tbl_login_ids_voy.source_system = 'asp') AND (dbo.tbl_login_ids_voy.user_id <> N'9999') AND (dbo.tbl_login_ids_voy.non_wsc_ind IS NULL)
AND (dbo.tbl_login_ids_voy.date_added =
(SELECT (MAX(date_added))
FROM dbo.tbl_login_ids_voy
WHERE dbo.tbl_login_ids_voy.user_id = dbo.tbl_Voy_Employee.SPTelsetID))
ORDER BY dbo.tbl_Voy_Employee.EmpFirstname

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 05:34:54

Give us more information as Peso asked for

Madhivanan

Failing to plan is Planning to fail

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-06-13 : 05:53:12

Rookie,

Please provide the information, but in the meantime, why not try adapting my query to your table, and then using that as a derived table in your query?

Rob Farley
http://robfarley.blogspot.com

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-06-13 : 06:56:05

Hi guys i got it sorted, i used a sub query .
Thanks for you help/advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources