selec min and max value with two columns

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

General SQL Server Forums

New to SQL Server Programming

selec min and max value with two columns

Author

Topic

djobert
Starting Member

2 Posts

Posted - 2012-04-20 : 09:18:57

Hi, I hava a table "T" with four columns : MAT(varchar(10)), NO_CIV(int), NO_CIV_S (varchar(5)), and NO_CIV_FULL which is a CONCAT of NO_CIV and NO_CIV_S

Here's an exemple of the basic querie.

SELECT MAT, CAST(NO_CIV AS VARCHAR)+' '+NO_CIV_S AS NO_CIV_FULL
FROM T

Here's the result

MAT---NO_CIV---NO_CIV_S---NO_CIV_FULL
123----6-----------------------6
235----4-----------------------4
246----2-----------------------2
246----5----------a------------2 a
246----5----------b------------2 b
246----8-----------------------8
865----3-----------------------3
999----2-----------------------2
999----4-----------------------4
999----6-----------------------6
999----7-----------------------7
999----9-----------------------9

What I need is this : When there are identical values in MAT column, I need to know what is the lower and the higher value in the NO_CIV_FULL column.

So, it should give me this result in a new table

MAT---LOWER---HIGHER
123-----6-------6
235-----4-------4
246-----2-------8
865-----3-------3
999-----2-------9

it's kind of "from to" function I need to get the interval between lower and higher value if many rows have the same value in the "MAT" column.

thanks a lot

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-20 : 09:46:08

I didn't follow the logic/reason for the concatenation, but since you have the MAT column and the NO_CIV column, you should be able to just take the min and max of the NO_CIV column.


SELECT
   MAT,
   MIN(NO_CIV) as LOWER,
   MAX(NO_CIV) as HIGHER
FROM
   T
GROUP BY
   MAT
ORDER BY
   MAT;

djobert
Starting Member

2 Posts

Posted - 2012-04-20 : 11:04:54

Perfect! it worked! Thanks a lot!

But if you want to know, these information are door numbers. So, if for a land parcel I have multiple adresses, I need to know the lowest and the higest. The concatenation is in case of apartments. if there is a "5a" and a "5b" on the parcel 246, I need to know it. If I use NO_CIV only, I'll have a low 5 and a high 5. But the real numbers are low "5a" and high "5b". Understand the logic?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources