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 2005 Forums
 Transact-SQL (2005)
 Selecting records in same table

Author  Topic 

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-05 : 12:20:29
Hi There,

This is hard to explain but I will try.

In my ordertable the Fields are:
AutoId
grpId
dateEntered
itemType

The table has groups of records that have the same value within a field called grpId. So 5 records could have grpId=10

What I need to do is query into the table using the Autoid, get the grpId of that record and then get a date from 1 of the 5 records that has an itemType of 6

pseudo:
Select grpId from ordertable where Autoid = 123
Select dateEntered from ordertable where itemType = 6 AND grpId = "result from select above"

Can that be done?

Thanks for your help.

Best Regards,






Always Learning.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-11-05 : 17:56:09
[code]select max(dateEntered) dateEntered -- Could use Min; we're just limiting it to a single row out of the five
from ordertable
where grpId = (select grpId from ordertable where Autoid = 123)[/code]OR[code]select max(ot.dateEntered) dateEntered -- Could use Min; we're just limiting it to a single row out of the five
from ordertable ot
inner join
ordertable ot1
on ot1.grpId = ot.grpId
and ot1.Autoid = 123[/code]OR[code]declare @grpId int

select @grpId = grpId
from ordertable
where Autoid = 123;

select max(dateEntered) dateEntered -- Could use Min; we're just limiting it to a single row out of the five
from ordertable
where grpId = @grpId[/code]



No amount of belief makes something a fact. -James Randi
Go to Top of Page

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-06 : 03:23:20
Hi Bustaz,
Thanks for the reply and the code.

I am not looking for the Max Date but I need the date from the record with itemType = 6

Best Regards,

Always Learning.
Go to Top of Page
   

- Advertisement -