question

itzpriya21 avatar image
itzpriya21 asked

Display the records based on year

Hi, I have a problem which I am working now. I would like to get some advise to get the expected results. DECLARE @t1 TABLE(RowNo INT IDENTITY(1,1),R1 VARCHAR(50), R2 VARCHAR(50), R3 INT) INSERT @t1(R1, R2, R3) VALUES('1','a',2006) INSERT @t1(R1, R2, R3) VALUES('1','b',2006) INSERT @t1(R1, R2, R3) VALUES('2','a',2007) INSERT @t1(R1, R2, R3) VALUES('2','b',2008) INSERT @t1(R1, R2, R3) VALUES('3','a',2006) INSERT @t1(R1, R2, R3) VALUES('3','b',2006) INSERT @t1(R1, R2, R3) VALUES('4','a',2006) INSERT @t1(R1, R2, R3) VALUES('4','b',2007) when i run the following query `SELECT r1,r2,MIN(r3) FROM @t1 GROUP BY r1,r2 ORDER BY r1`, I got the following results. r1 r2 (No column name) --- --- ------------- 1 a 2006 1 b 2006 2 a 2007 2 b 2008 3 a 2006 3 b 2006 4 a 2006 4 b 2007 but I am expecting the following results. r1 r2 (No column name) --- --- ------------- 1 a 2006 1 b 2006 2 a 2007 2 b 2007 3 a 2006 3 b 2006 4 a 2006 4 b 2006 is there a way to achieve this results?
t-sqldates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
What's happening in your query is that it's looking for the minimum r3 for each combination of r1 & r2; what you're looking for is each combination of r1 & r2 with the minimum r3 for each value of r1 across all values of r2. One way of doing this is as a subquery, such as this: select distinct r1, r2, (select MIN (r3 ) from @t1 t_inner where t_inner.R1 = t_outer.R1) from @t1 t_outer
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

itzpriya21 avatar image itzpriya21 commented ·
Oh... Subquery..Ya.. It can make the difference. Thanks for that... I didn't think about the subquery. Thank you very much for your solution. I am exactlly want this result only. It can be closed now.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
you could put a thumbs-up on the solution? and a tick to mark it as correct?
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
I'm only on WP7 at the moment, but wouldn't something like the following also work: select r1,r2,min(r3) over(partition by r1) as r3 from @t1
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TomJerry avatar image TomJerry commented ·
'Order by' gives error. The same query without Order By clause works fine select r1,r2,min(r3) over(partition by r1) as r3 from @t1
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Of course, you're right. Aggregates cannot have an order by, I have edited my answer to reflect that.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.