write select 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 2005 Forums

Transact-SQL (2005)

write select query

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-06-07 : 05:36:19

hi
i have following two Master and Detail table structure

Master Details
itemnm wt date itemnm wt date
item1 10.2 6/7/2011 item1 20.6 6/6/2011
item2 20.3 6/7/2011 item1 50.2 6/5/2011
item3 5.5 6/7/2011 item1 20 6/4/2011
item4 10.2 6/7/2011 item1 40.3 6/3/2011
item2 10.6 6/6/2011
item2 50.2 6/4/2011
item2 20 6/4/2011
item2 40.3 6/2/2011
item3 25.0 6/5/2011
item3 50.2 6/3/2011
item3 20 6/4/2011
item3 40.3 6/1/2011
item4 30.0 6/6/2011
item4 50.2 6/5/2011
item4 20 6/4/2011
item4 40.3 6/3/2011

i have select all record in master table and select top 1 wt colom to order by date desc order in detail table to match the item colom. i would like following result

itemnm wt date previous wt
item1 10.2 6/7/2011 20.6
item2 20.3 6/7/2011 10
item3 5.5 6/7/2011 25
item4 10.2 6/7/2011 30

so how to write this query

thanks in advance

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-07 : 06:04:53

Select * from(select *,row=row_number() over (partition by datet order by datet) from @t)t where row=1

In ( ) put your full query and add /* ,row=row_number() over (partition by date order by date)*/ after * Like this

*,row=row_number() over (partition by datet order by datet)

This works with MSSQL 2005,2008

In Love... With Me!

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-06-07 : 06:12:07

thanks to reply dear
but i don't understand your query . i am using sql2005

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-07 : 06:17:11

ROW_NUMBER(): return a number with each row in a group, starting at 1

In Love... With Me!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-07 : 06:22:14

you need to add
,row=row_number() over (partition by datet order by datet) after your select *

and put your full query in another select * from ( --- Here--- )t where row=1

Or post me your working query I will send you back

In Love... With Me!

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-06-07 : 06:34:04

i think you are not understand my requirement
i have select all record in master table and I have select wt column of details table .which record insert by last date like previous wt to particular item in master table

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-07 : 07:03:14

Click and see this link to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Or see this solution help

Declare @t table (id int,datet datetime)
Insert into @t
Select 1,getdate()-15 Union all
Select 2,getdate()-14 Union all
Select 3,getdate()-14 Union all
Select 4,cast('19841220' as datetime) Union all
Select 5,cast('19841220' as datetime)

Select * from(select *,row=row_number() over (partition by datet order by datet) from @t)t where row=1

Select * from @t

In Love... With Me!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-07 : 08:58:21

Post full data

--Create table [Master](itemnm nvarchar(23),wt numeric(10,2), date datetime)
--Create Table [Details] (itemnm nvarchar(23),wt numeric(10,2), date datetime)
--Insert into [Master]
----Select 'item1', 10.2, cast('2011-7-6' as datetime) union all
--Select 'item2', 20.3, cast('2011-7-6' as datetime) union all
--Select 'item3', 5.5, cast('2011-7-6' as datetime) union all
--Select 'item4', 10.2, cast('2011-7-6' as datetime) union all
--Select 'item2',10.6, cast('2011-6-6' as datetime) union all
--Select 'item2',50.2, cast('2011-6-4' as datetime) union all
--Select 'item2',20, cast('2011-6-4' as datetime) union all
--Select 'item2',40.3, cast('2011-6-2' as datetime) union all
--Select 'item3',25.0, cast('2011-6-5' as datetime) union all
--Select 'item3',50.2, cast('2011-6-3' as datetime) union all
--Select 'item3',20, cast('2011-6-5' as datetime) union all
--Select 'item3',40.3, cast('2011-6-6' as datetime) union all
--Select 'item4',30.0, cast('2011-6-6' as datetime) union all
--Select 'item4',50.2, cast('2011-6-5' as datetime) union all
--Select 'item4',20, cast('2011-6-4' as datetime) union all
--Select 'item4',40.3, cast('2011-6-3' as datetime)
--
--
--insert into [Details]
--Select 'item1', 20.6, cast('2011-6-6' as datetime) union all
--Select 'item1', 50.2, cast('2011-6-5' as datetime) union all
--Select 'item1', 20, cast('2011-6-4' as datetime) union all
--Select 'item1', 40.3, cast('2011-6-3' as datetime)

Select t.itemnm,t.wt,t.date,tt.wt from
(Select *,row=row_number() over(partition by itemnm order by date desc) from [Master])t
left join
(select *,row2=row_number() over(partition by itemnm order by date desc) from [Details])tt on t.itemnm=tt.itemnm
where tt.row2=1 or tt.row2 is null and t.row=1

In Love... With Me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources