MIn and Max or Range

Question

Hi, I have a table in SQL like so UId Class 1 Apple 2 Apple 3 Apple 3a Apple 4 Orange 4a Orange 4b Orange 5 Orange I am trying to get the min and max of the UId column for each class. Is it possible to do so, as the UId is varchar. Thanks

Nitin Sontakke · Answer

Excellent!

Joginder Banger · Answer

CREATE table #Tempvalue(uid varchar ( max ),class varchar ( max )) insert into #Tempvalue VALUES ( '1' , 'Apple' ) insert into #Tempvalue VALUES ( '2' , 'Apple' ) insert into #Tempvalue VALUES ( '3' , 'Apple' ) insert into #Tempvalue VALUES ( '3a' , 'Apple' ) insert into #Tempvalue VALUES ( '4a' , 'Orange' ) insert into #Tempvalue VALUES ( '4a' , 'Orange' ) insert into #Tempvalue VALUES ( '6b' , 'Orange' ) insert into #Tempvalue VALUES ( '5z' , 'Orange' ) select [Class], min ([UId]) [UIdMin], max ([Uid]) [UIdMax] from [dbo].#Tempvalue group by [Class] Class UIdMin UIdMax Apple 1 3a Orange 4a 6b

sudipta sanyal · Answer

Hi, Try This CREATE TABLE #tempUID (UId VARCHAR(10), NAME NVARCHAR(20)) INSERT INTO #tempUID SELECT '1','Apple' INSERT INTO #tempUID SELECT '2','Apple' INSERT INTO #tempUID SELECT '3','Apple' INSERT INTO #tempUID SELECT '3a','Apple' INSERT INTO #tempUID SELECT '4','Orange' INSERT INTO #tempUID SELECT '4a','Orange' INSERT INTO #tempUID SELECT '4b','Orange' INSERT INTO #tempUID SELECT '5','Orange' SELECT MaxID = MAX(UId),MinID = MIN(UId) FROM #tempUID GROUP BY NAME DROP TABLE #tempUID

Nitin Sontakke · Answer

Yes, but UId being varchar you will get whatever SQL Server considers to be min and max on the basis of it's ascii value and not the numeric value. For example, 9 will be bigger than 20. You don't have any choice, of course. select [Class], min ([UId]) [UIdMin], max ([Uid]) [UIdMax] from [dbo].[tableName] group by [Class]

Sunshine · Answer

From all the expert adivces I get here, I think I should not do what I intend to do. As it is a ASP.NET Webforms application using VB.NET, I retrieved all the records from the table, and inserted into a dictionary, then played with it. So now I get the result I want.

Sunshine · Answer

That's right. None of these will work as it's varchar and the result shows, for example, 100, then 10 if I sort asc

Nitin Sontakke · Answer

@Joginder, Copying top part of one reply and bottom part of another and constructing yours, without typing a single word (except probably to press F5 to execute the query) is not quite creative.

UId	Class
1	Apple
2	Apple
3	Apple
3a	Apple
4	Orange
4a	Orange
4b	Orange
5	Orange

MIn and Max or Range

Answers (7)