Need a SQL Query

correction

with cte as
(
  select a.itemnumber, b.itemdescription, a.itemcode, 
     row_number() over (partition by a.itemNumber order by b.createdatetime desc) rn
  from items a
  inner join itemsdetails b on b.itemnumber = a.itemnumber
)

select ItemNumber, ItemDescription, ItemCode
from cte
where rn = 1
and ItemDescription = 'Test2'

Select allOpen in new window

oops sorry, the first one is correct

......I don't want to do your homework but here is some help.


you will always select what fields you want first. So if you look at your answer you have 3 fields: T1.Itemnumber, T2.Itemdescription, T1.ItemCode so you know you will want to select these.... The T1 and T2 before the field names are just explicitly telling sql to choose table1's itemnumber since both tables have Itemnumber as a field.


select T1.Itemnumber, T2.Itemdescription, T1.ItemCode from....... this is where you list what tables you are getting the data from, you only have 2 tables so it will be from table1, table 2.

Now you have the fields(select) from (your tables) where...... the last thing is to join the tables in the where clause. If you just selected the fields without a join you would get all the records which you don't want. So join them....where t1.itemnumber = t2.itemnumer and (this is where we have ItemDescription with “Test2” ) t2.Itemdescription= "Test2";


You do not need the T2.ItemDescription since it is only on table 2 however it makes it easier to find later if you need to. You will need to put it all together and if you may also need to look at the unique constraint if you get multiple results.

and one more post :) if it is already posted, please ignore it...

select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
		where ItemDescription='Test2'
	) x where rn=1
) t2 on t1.itemnumber=t2.itemnumber

Select allOpen in new window

Experts,

Sincere Thanks for the quick reply and appreciate all your help and responses.

I have tried all the queries and first one from "ewangoya" is the only query working as expected.

"ewangoya" : I am trying to modify your query to get the rows only has "test2" and couldn't get it.
The output I was looking for is = 6 Test2 AG

Thanks again for all yoru help.

for that type of query you would need to add a not exists subquery test....

if you do not tell us where/how our queries failed we cannot help.

you are now modifying your original question and technically that requires you to open a further question in  EE.

I got the question wrong I guess

try this (assuming CreateDatetime is unique, ow there is no way to find the first item, somehow we should find an order, your sample does not have time part, but if they have the query will work fine)

select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
	) x where rn=1 and ItemDescription='Test2'
) t2 on t1.itemnumber=t2.itemnumber

ItemNumber	ItemDescription	ItemCode
2	Test2	AA
3	Test2	BR
4	Test2	CV
6	Test2	AG

Select allOpen in new window

and here is the demo

with table1 as(
select 1 ItemNumber, 'AG' ItemCode
union select 2,'AA'
union select 3,'BR'
union select 4,'CV'
union select 5,'AA'
union select 6,'AG'
)
,
table2 as (
SELECT 1 ItemNumber, 'Test1' ItemDescription, '20110807 00:00:00' CreateDatetime
union select 1, 'Test2','20110807 00:00:01'
union select 1, 'Test2','20110807 00:00:02'
union select 2, 'Test2','20110808 00:00:03'
union select 2, 'Test2','20110808 00:00:04'
union select 3, 'Test2','20110809 00:00:05'
union select 3, 'Test1','20110809 00:00:06'
union select 3, 'Test2','20110809 00:00:07'
union select 4, 'Test2','20110810 00:00:08'
union select 4, 'Test2','20110810 00:00:09'
union select 5, 'Test1','20110811 00:00:10'
union select 6, 'Test2','20110812 00:00:11'
)
select T1.ItemNumber,	T2.ItemDescription,	T1.ItemCode
from Table1 t1 inner join 
(
	select * from (
		select *, row_number() over(partition by itemNumber order by CreateDatetime) rn 
		from Table2
	) x where rn=1 and ItemDescription='Test2'
) t2 on t1.itemnumber=t2.itemnumber

ItemNumber	ItemDescription	ItemCode
2	Test2	AA
3	Test2	BR
4	Test2	CV
6	Test2	AG

Select allOpen in new window