- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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
Comment

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

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.

you could put a thumbs-up on the solution? and a tick to mark it as correct?

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

No one has followed this question yet.

Copyright 2019 Redgate Software.
Privacy Policy