Sql Query Help

[dotnetrockz]

naku oka table variable lo data undhi like say ID column

 

iam joining this table to other subquery(inner joins b/w set of tables) to update some columns say a datecol in the table variable

 

 

now when i join with id as a common col for 2 tables i want to read the top 1 date for each id using join...how do i do that?

[upendra_super]

ltt

[ChampakDas]

group by that id and max(date) kodithe vachestundi kada

naku oka table variable lo data undhi like say ID column

 

iam joining this table to other subquery(inner joins b/w set of tables) to update some columns say a datecol in the table variable

 

 

now when i join with id as a common col for 2 tables i want to read the top 1 date for each id using join...how do i do that?

 

 

[dotnetrockz]

group by that id and max(date) kodithe vachestundi kada

 

 

 

 

naa query ila unhdi

 

 

UPDATE table1

SET LastworkingDate=t.LastworkingDate

FROM @table1_t table1INNER JOIN

(

  select

convert(VARCHAR(10),er.complete_dt,101) as 'LastworkingDate'

from table2 inner join

@table1 on table2.id=table1.id

where

inner join table3

on table2.woid=table3.woid

......

where table7.val='' ' and table1.status=2

)t

 

on t.id=table1.id

 

 

indhulo nenu max and groupby add chesthe will it read the max value for each row in table1?

[ChampakDas]

as long as the date and id belong to table1, yes

naa query ila unhdi

 

 

UPDATE table1

SET LastworkingDate=t.LastworkingDate

FROM @table1_t table1INNER JOIN

(

  select

convert(VARCHAR(10),er.complete_dt,101) as 'LastworkingDate'

from table2 inner join

@table1 on table2.id=table1.id

where

inner join table3

on table2.woid=table3.woid

......

where table7.val='' ' and table1.status=2

)t

 

on t.id=table1.id

 

 

indhulo nenu max and groupby add chesthe will it read the max value for each row in table1?

 

[Suhaas]

naku oka table variable lo data undhi like say ID column

 

iam joining this table to other subquery(inner joins b/w set of tables) to update some columns say a datecol in the table variable

 

 

now when i join with id as a common col for 2 tables i want to read the top 1 date for each id using join...how do i do that?

 

Did you try rank(row num) based on partition by?

 

Example

 

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY DateCreated DESC) AS rn
FROM Table1
)
SELECT *
FROM cte
WHERE rn = 1

[dotnetrockz]

as long as the date and id belong to table1, yes

sorry i confused u...date nenu table 3 nunchi read chesi iam assiging it to table 1 date column.....but let me try this first

[dotnetrockz]

Did you try rank(row num) based on partition by?

 

Example

 

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY DateCreated DESC) AS rn
FROM Table1
)
SELECT *
FROM cte
WHERE rn = 1

iam using a join bhayya...ee rank number ela fit avuthundho aa query lo?

[dotnetrockz]

Thanks GatisKandis & Seven

 

looks like the group by is working but some of the id's have wrong data...i 'll check how i can improv ethe query to read correct data

[Suhaas]

Thanks GatisKandis & Seven

 

looks like the group by is working but some of the id's have wrong data...i 'll check how i can improv ethe query to read correct data

 

Ok bhayya. Cool. 

 

On a totally different note. You can use Partition by/Rank in a Join. Chala useful feature. You might want to look it up some time. 

 

S%Hi

[ChampakDas]

yes if group by dint work as Seven said Rank use cheste best - this way even before your update condition once you have ranks you can just select records with Rank:1 and update the table variable

Thanks GatisKandis & Seven

 

looks like the group by is working but some of the id's have wrong data...i 'll check how i can improv ethe query to read correct data

 

 

Ok bhayya. Cool. 

 

On a totally different note. You can use Partition by/Rank in a Join. Chala useful feature. You might want to look it up some time. 

 

S%Hi

 

[DaleSteyn1]

[150bryant]

when dealing with subqueries or inner logics..

get the data out....

 

antey get the required columns out of the whole logic.

this will isolate your problem..

 

then on those columns do whatever you want...

this might become a big query but u will hav clarity

[Johny]

simple thatha...delete * from tbl_name;