MIn and Max or Range

Sunshine

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Excellent!
+1
Sunshine8y ago
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.
0
Sunshine8y ago
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
0
Nitin Sontakke8y ago
@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.
0
Joginder Banger8y ago
1. CREATE table #Tempvalue(uid varchar(max),class varchar(max))
2. insert into #Tempvalue VALUES('1','Apple')
3. insert into #Tempvalue VALUES('2','Apple')
4. insert into #Tempvalue VALUES('3','Apple')
5. insert into #Tempvalue VALUES('3a','Apple')
6. insert into #Tempvalue VALUES('4a','Orange')
7. insert into #Tempvalue VALUES('4a','Orange')
9. insert into #Tempvalue VALUES('6b','Orange')
11. insert into #Tempvalue VALUES('5z','Orange')
13. select [Class], min([UId]) [UIdMin], max([Uid]) [UIdMax]
14. from [dbo].#Tempvalue
15. group by [Class]
1. Class UIdMin UIdMax
2. Apple 1 3a
3. Orange 4a 6b
+1

sudipta sanyal8y ago

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 Sontakke8y ago

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]