Query Problem - 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

SQL Server Development (2000)

Query Problem

Author

Topic

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 13:59:22

I have one of my tables that looks like this:

ID NAME POSITION LAST_UPDATE
981 GAMENG LORA 13 6/23/2007 12:00 AM
981 GAMENG LORA 26 3/31/2007 12:00 AM
981 GAMENG LORA 05 7/11/2007 12:00 AM

I need to generate a query that give the LAST_UPDATE and the POSITION number including the rest of the columns. I already tried GROUP BY and MAX, but I can not get the right POSITION number.

Thank you very much

Jd

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 14:44:00

What is the expected output?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 14:53:52

Basicaly what I need are these results:

ID NAME POSITION LAST_UPDATE
981 GAMENG LORA 05 7/11/2007 12:00 AM

Only one row with the last update of the three rows and the corresponding position number for this row.

Again, thanks

Jd

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 14:58:14

[code]

Declare @T table (ID int, NAME varchar(50), POSITION varchar(3), LAST_UPDATE datetime)
insert into @T
select 981, 'GAMENG LORA' ,'13', '6/23/2007 12:00 AM' union all
select 981, 'GAMENG LORA' ,'26', '3/31/2007 12:00 AM' union all
select 981, 'GAMENG LORA' ,'05', '7/11/2007 12:00 AM'

Select T1.ID, T1.NAME , T1.POSITION , T2.LastMaxDate
from @T T1
Join ( select Id, Name, max(last_update) as LastMaxDate
from @T
Group by Id, Name
) T2 On T1.LAST_UPDATE = T2.LastMaxDate And T1.ID = T2.id And T1.NAME = T2.name

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 15:47:30

It is working but is duplicating rows with the same exactly values.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 16:19:24

What do you mean by "but is duplicating rows with the same exactly values."

Did you actually run my query? It gives the same output you wanted. Or do you have more data that the query is returning incorrect results. If so post more data.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 17:56:02

I have something like this in my original records:

ID, NAME, POSITION, LAST_UPDATE
011, RICHARD, 552, 09-DEC-06
881, MARK, 867, 09-DEC-06
881, MARK, 845, 25-DEC-06
881, MARK, 849, 04-FEB-07
839, SON MARK, 554, 09-DEC-06
011, RICHARD, 558, 09-OCT-05
011, RICHARD, 552, 10-NOV-06
129, CON EUGENE, 471, 09-DEC-06
207, LINDA, 113, 23-DEC-06
543, TRACY, 011, 23-DEC-06
543, TRACY, 267, 20-DEC-05
188, ANTHONY, 321, 23-DEC-06

If you can see there is duplicated IDs with their corresponding NAME and each of these records have different POSITION and DATE. What need is to get from al this records is ID, NAME and POSITION for only the most recently updated record on each name. Like so:

ID, NAME, POSITION, LAST_UPDATE
011, RICHARD, 552, 09-DEC-06 (last record inserted for RICHARD)
881, MARK, 849, 04-FEB-07 (last record inserted for MARK)
839, SON MARK, 554, 09-DEC-06
129, CON EUGENE, 471, 09-DEC-06
207, LINDA, 113, 23-DEC-06
543, TRACY, 011, 23-DEC-06 (last record inserted for TRACY)
188, ANTHONY, 321, 23-DEC-06

Thanks for your help!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 18:05:28

Well I got the same result as you wanted when I re-ran the query with the new data:


Declare @T table (ID varchar(5), NAME varchar(50), POSITION varchar(3), LAST_UPDATE datetime)
insert into @T 
select '011', 'RICHARD', '552', '09-DEC-06' union all
select '881', 'MARK', '867', '09-DEC-06' union all
select '881', 'MARK', '845', '25-DEC-06' union all
select '881', 'MARK', '849', '04-FEB-07' union all
select '839', 'SON MARK', '554', '09-DEC-06' union all
select '011', 'RICHARD', '558', '09-OCT-05' union all
select '011', 'RICHARD', '552', '10-NOV-06' union all
select '129', 'CON EUGENE', '471', '09-DEC-06' union all
select '207', 'LINDA', '113', '23-DEC-06' union all
select '543', 'TRACY', '011', '23-DEC-06' union all
select '543', 'TRACY', '267', '20-DEC-05' union all
select '188', 'ANTHONY', '321', '23-DEC-06'

Select T1.ID, T1.NAME , T1.POSITION , T2.LastMaxDate
from @T T1
Join ( select Id, Name, max(last_update) as LastMaxDate
	from @T
	Group by Id, Name
	) T2 On T1.LAST_UPDATE = T2.LastMaxDate And T1.ID = T2.id And T1.NAME = T2.name

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 18:31:09

Thank you very much, this looks very good, but one more thing that I missed. It looks like in my orginal data and only for this columns(ID, NAME, POSITION and DATE) are duplicated records. What can I do?

881, MARK, 867, 09-DEC-06
881, MARK, 867, 09-DEC-06
881, MARK, 867, 09-DEC-06
881, MARK, 845, 25-JAN-05 (different)

Thanks again,

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 19:13:25

I didnt understand your question. Can you rephrasE?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-17 : 21:29:48

What you did with the join was perfect, but I missed the fact that the original records contain rows with same exactly data (ID, NAME, POSISTION and DATE ) what results after the join you did with duplicates. This normaly should be resolved with a group by, but it is not working in this case.

These are the results after your join:

881, MARK, 867, 09-DEC-06
881, MARK, 867, 09-DEC-06
881, MARK, 867, 09-DEC-06

Marks data is correct because that is the most recent information we have about him, but we just need one row.

Thanks you very much

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 21:36:39

Try adding a GROUP BY for the outer query.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jdelarco
Starting Member

8 Posts

Posted - 2007-07-18 : 08:09:15

Thank you very much dinakar, now everything looks fine after the group by.

Again, Thanks

Jd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources