Need help with a query... - 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)

Need help with a query...

Author

Topic

kekikamea
Starting Member

3 Posts

Posted - 2003-11-19 : 20:43:27

I did a join on two tables to get the following results. I saved the results in a #temptable.

id table2id table2descrip dateinserted
====================================================
1 3 descrip1 11/3/2002
2 4 descrip2 11/2/2002
3 3 descrip1 11/4/2001
4 3 descrip1 10/5/2003
5 4 descrip2 12/8/2001
6 5 descrip3 9/10/2002

I want to query that #temptable to get the max date for each table2id and only return that record. So I need a query to get the follow results...

id table2id table2descrip dateinserted
====================================================
2 4 descrip2 11/2/2002
4 3 descrip1 10/5/2003
6 5 descrip3 9/10/2002

Question...
What query can I make with #temptable to give me the results?

NOTE: I posted this question on a newsgroup, however, after doing that I realized that I should have posted here instead. So if you see the question on the NG, please forgive me for posting again.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-11-19 : 20:51:57

Do you have to have the ID column? Because it will throw off a group by. Otherwise, you could just do

SELECT table2id,table2descrip, max(dateinserted) as dateinserted from #temptable Group by table2id,table2descrip

Sarah Berger MCSD

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 21:00:43

[code]

select *
from #temptable t1
where dateinserted = (select max(dateinserted) from #temptable t2 where t1.table2id = t2.table2id)
order by id
[/code]

kekikamea
Starting Member

3 Posts

Posted - 2003-11-19 : 21:57:05

thanks simondeutsch... your query worked perfectly.

ehorn... i appreciate your help too, however, the select only returned one record.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 22:19:59

quote:
ehorn... i appreciate your help too, however, the select only returned one record.

That is odd

It works perfectly for me???

Can you tell me what version of SQL and service packs you are using?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-19 : 22:42:01

Man, this is like the third incident of differing Service Packs producing different results!

I think I need to stay on top of SP updates!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 22:43:17

What concerns me is that I do alot of development on my laptop
which is SQL2000 SP3a and our servers at work are not up to sp3a yet.
I'll have to see what I can do about lighting a candle under someones A#@ at work or roll back my sp to be in compliance.

kekikamea
Starting Member

3 Posts

Posted - 2003-11-20 : 02:45:43

Hi ehorn...

we are using sqlsvr 2K sp3. don't worry, maybe it was something i did wrong. i do appreciate your help. i will probably asking a lot more in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources