Hi and Thanks,

So this is the only way from which i can get the result (by union) ?

I should use UNION ALL in place of UNION.

Ok, thanks the steps made it clear now.

I never used over/partition in order by clause , i found it new..

Now i understood.

Thanks again.

Thanks again,

yes that works,

but still the flow goes above my head. :crying:

Thanks again

when i gave this input

B | 50

B | 40

A | 50

C | 20

B | 15

B | 10

A | 5

i got some ungrouped results.

Thanks it works ! 😛

so exactly the partittion is used in order by .. am i correct?

how will the sorting and grouping takes place in that case.. If you can...

thank you.

Thank you very very much Willem. You have solved it !

i used below code and it worked ,

[Code]

set datefirst 1

select

datepart(yy,date) as [Year],

dbo.ISOweek(date) as [Week],

Avg(Prices) as [Prices]

from tblPrices

group by...

Hi,

Thanks Willem for taking efforts to solve the prob...

Yes i went thru the links that you provided, and i found that iactually need ISO week.

Its given in BOL a nice...

wschampheleer (2/5/2010)

---

Hold your horses Gandalf! Who said anything about hard coding?

The value to subtract can easily be calculated dynamically and integrated into the query:

SELECT DATEPART(wk,DATEADD(dd, -(@@datefirst + 7 -...

wschampheleer (2/5/2010)

---

From BOL:

January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

One workaround...

Thank you wschampheleer. Some minor changes done and it worked.

Hi, Thanks Jack ofr your valuable suggestions. I would like to add few more details to my structure.

1. In tbl_StockMaster ,iStockId is primary key.

2. While in tbl_StockData there...

Hi,

But the one u said returns more than one row if all columns are same and date is different.

Hi,

I used this qurey on a table with 2Lakhs and 80 thousands records

[Code]

select a.sort, a.type, max(a.Date), b.Price, a.Name

from @tbl a

inner join (select sort, type, max(Price) Price

from @tbl

group by...

Hi, Sorry for late reply . i wasnt on desktop for a while..

continuing ..

cant i use this

[Code]

select sort,type,Max(price),Max(Date),name from @tbl group by sort,type,name

[/Code]