Selecting records in same table

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources