Min and Max or Range
-
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
-
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
sunsher wrote:
Is it possible to do so, as the UId is varchar.
Yes, but ... it's frankly pretty nasty, and going to make for a complicated query. If you want data that you can treat as numeric - and you will need to use it for numerics at some point, or your sort order will be completely wrong - I'd strongly suggest that you change the column, and split it into two parts: a numeric prefix, and an alphabetic suffix. That way, you can ignore your suffix when trying to work with numbers, and combine the columns to a strign when you want to work with the "original value". Then it's trivial:
SELECT MIN(numericBit), MAX(numericBit), Class
FROM MyTable
GROUP BY ClassBut trying to do that with string based data that may contain alphabetic suffixes? Nasty, nasty code.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
-
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
I am bluntly going to say that UId isn't. A primary key is not a varchar. I don't care about how you want to display it, it should not be stored like that. A Guid, fine. Integer, fine. No varchar. Change it to avoid more problems later :thumbsup:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
Like Griff said, that would result in some nasty SQL. Of course, the following works just fine:
-- just setting up a table so I could illustrate the necessary sql
IF OBJECT_ID('tempdb..#fruits') IS NOT NULL
DROP TABLE #fruitsCREATE TABLE #fruits
(
ID nvarchar(10),
Fruit nvarchar(16)
)insert into #fruits (ID, Fruit)
VALUES ('1', 'Apple'),
('2', 'Apple'),
('3', 'Apple'),
('3a', 'Apple'),
('4', 'Orange'),
('4a', 'Orange'),
('4b', 'Orange'),
('5', 'Orange')--///////////////////////////////////////////////////////////////
-- this is the code that does what you want
--///////////////////////////////////////////////////////////////SELECT MIN([ID]) AS MinID, MAX([ID]) AS MaxID, [Fruit] FROM [#fruits] GROUP BY [Fruit]
-- or if you want to strip the non-numeric chars
-- (warning - somewhat nasty sql ahead)SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]Beyond that, what if the data is mixed up (the data is put into the database as apple,orange,apple,apple,orange,apple)? Even if the data WERE ordered exactly the way you want it, what business case would find the min/max Uid as relevant data? Even if you were using a more appropriate integer ID field, it still wouldn't mean anything to anyone. [Minor_Rant] Programming instructors really need to start creating meaningful assignments so that a) the student doesn't learn from a basis of bad coding practice, and b) the student isn't pummeled with whacked out scenarios that would never happen in the real world. The small amount of actual college I attended (after having been a programmer for 10 years) really ticked me off. One C++ assignment involved counting the number of a certain character in a string. I provided three different ways to do it, but I was marked down (I actually got an 'F' for the assignment) for the content of the string I used. I contested it, and got my 'A' grade, but the whole thing left me with a real sour taste in my mouth for "instructors" of any description. [/Minor_Rant]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
- -
Like Griff said, that would result in some nasty SQL. Of course, the following works just fine:
-- just setting up a table so I could illustrate the necessary sql
IF OBJECT_ID('tempdb..#fruits') IS NOT NULL
DROP TABLE #fruitsCREATE TABLE #fruits
(
ID nvarchar(10),
Fruit nvarchar(16)
)insert into #fruits (ID, Fruit)
VALUES ('1', 'Apple'),
('2', 'Apple'),
('3', 'Apple'),
('3a', 'Apple'),
('4', 'Orange'),
('4a', 'Orange'),
('4b', 'Orange'),
('5', 'Orange')--///////////////////////////////////////////////////////////////
-- this is the code that does what you want
--///////////////////////////////////////////////////////////////SELECT MIN([ID]) AS MinID, MAX([ID]) AS MaxID, [Fruit] FROM [#fruits] GROUP BY [Fruit]
-- or if you want to strip the non-numeric chars
-- (warning - somewhat nasty sql ahead)SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]Beyond that, what if the data is mixed up (the data is put into the database as apple,orange,apple,apple,orange,apple)? Even if the data WERE ordered exactly the way you want it, what business case would find the min/max Uid as relevant data? Even if you were using a more appropriate integer ID field, it still wouldn't mean anything to anyone. [Minor_Rant] Programming instructors really need to start creating meaningful assignments so that a) the student doesn't learn from a basis of bad coding practice, and b) the student isn't pummeled with whacked out scenarios that would never happen in the real world. The small amount of actual college I attended (after having been a programmer for 10 years) really ticked me off. One C++ assignment involved counting the number of a certain character in a string. I provided three different ways to do it, but I was marked down (I actually got an 'F' for the assignment) for the content of the string I used. I contested it, and got my 'A' grade, but the whole thing left me with a real sour taste in my mouth for "instructors" of any description. [/Minor_Rant]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-Slight simplification of the last query:
SELECT
MIN(N.JustNumeric) AS MinID,
MAX(N.JustNumeric) AS MaxID,
F.[Fruit]
FROM
[#fruits] As F
CROSS APPLY ( SELECT substring(F.[ID], PATINDEX('%[0-9]%', F.[ID]), 1 + PATINDEX('%[0-9][^0-9]%', F.[ID] + 'x') - PATINDEX('%[0-9]%', F.[ID])) ) As N (JustNumeric)
GROUP BY
F.[Fruit]
;Still nasty, but at least the "computed column" doesn't have to be repeated. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
"These people looked deep within my soul and assigned me a number based on the order in which I joined" - Homer
-
Slight simplification of the last query:
SELECT
MIN(N.JustNumeric) AS MinID,
MAX(N.JustNumeric) AS MaxID,
F.[Fruit]
FROM
[#fruits] As F
CROSS APPLY ( SELECT substring(F.[ID], PATINDEX('%[0-9]%', F.[ID]), 1 + PATINDEX('%[0-9][^0-9]%', F.[ID] + 'x') - PATINDEX('%[0-9]%', F.[ID])) ) As N (JustNumeric)
GROUP BY
F.[Fruit]
;Still nasty, but at least the "computed column" doesn't have to be repeated. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Slight simplification of the last query:
SELECT
MIN(N.JustNumeric) AS MinID,
MAX(N.JustNumeric) AS MaxID,
F.[Fruit]
FROM
[#fruits] As F
CROSS APPLY ( SELECT substring(F.[ID], PATINDEX('%[0-9]%', F.[ID]), 1 + PATINDEX('%[0-9][^0-9]%', F.[ID] + 'x') - PATINDEX('%[0-9]%', F.[ID])) ) As N (JustNumeric)
GROUP BY
F.[Fruit]
;Still nasty, but at least the "computed column" doesn't have to be repeated. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Dude, that's just a thing of beauty right there. Almost as good as a macro. :)
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
If you're working in VB, why did you post your question in the C# forum?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
To repeat myself; the way you load it is not a problem. The way you store it is; go read up on what a primary key is.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Like Griff said, that would result in some nasty SQL. Of course, the following works just fine:
-- just setting up a table so I could illustrate the necessary sql
IF OBJECT_ID('tempdb..#fruits') IS NOT NULL
DROP TABLE #fruitsCREATE TABLE #fruits
(
ID nvarchar(10),
Fruit nvarchar(16)
)insert into #fruits (ID, Fruit)
VALUES ('1', 'Apple'),
('2', 'Apple'),
('3', 'Apple'),
('3a', 'Apple'),
('4', 'Orange'),
('4a', 'Orange'),
('4b', 'Orange'),
('5', 'Orange')--///////////////////////////////////////////////////////////////
-- this is the code that does what you want
--///////////////////////////////////////////////////////////////SELECT MIN([ID]) AS MinID, MAX([ID]) AS MaxID, [Fruit] FROM [#fruits] GROUP BY [Fruit]
-- or if you want to strip the non-numeric chars
-- (warning - somewhat nasty sql ahead)SELECT MIN(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MinID,
MAX(substring([ID], PATINDEX('%[0-9]%', [ID]), 1+PATINDEX('%[0-9][^0-9]%', [ID]+'x')-PATINDEX('%[0-9]%', [ID]))) AS MaxID,
[Fruit]
FROM [#fruits]
GROUP BY [Fruit]Beyond that, what if the data is mixed up (the data is put into the database as apple,orange,apple,apple,orange,apple)? Even if the data WERE ordered exactly the way you want it, what business case would find the min/max Uid as relevant data? Even if you were using a more appropriate integer ID field, it still wouldn't mean anything to anyone. [Minor_Rant] Programming instructors really need to start creating meaningful assignments so that a) the student doesn't learn from a basis of bad coding practice, and b) the student isn't pummeled with whacked out scenarios that would never happen in the real world. The small amount of actual college I attended (after having been a programmer for 10 years) really ticked me off. One C++ assignment involved counting the number of a certain character in a string. I provided three different ways to do it, but I was marked down (I actually got an 'F' for the assignment) for the content of the string I used. I contested it, and got my 'A' grade, but the whole thing left me with a real sour taste in my mouth for "instructors" of any description. [/Minor_Rant]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-John Simmons / outlaw programmer wrote:
I actually got an 'F' for the assignment) for the content of the string
Using "youareafuckingidiot" as your content will do that. :laugh:
Never underestimate the power of human stupidity RAH
-
John Simmons / outlaw programmer wrote:
I actually got an 'F' for the assignment) for the content of the string
Using "youareafuckingidiot" as your content will do that. :laugh:
Never underestimate the power of human stupidity RAH
:) Well, the instructor did not provide the string (a curiosity because it would have made evaluating the output from code much easier since everyone would have to have the same count), so I wrote a paragraph about the diminishing hemlines in college girls' skirts during summer classes.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
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 OrangeI am trying to get the min and max of the UId column for each class as below.
1 - 3 Apple
4 - 5 OrangeIs it possible to do so, as the UId is varchar. Thanks
Can it be done; yes. Should it be done; not at this time. Really should fix the database design first. 1. You should have a Good Primary Key. UID in its current state is not. 2. Class should be normalized- put it into its own table and link it back via another Good Primary Key
Director of Transmogrification Services Shinobi of Query Language Master of Yoda Conditional
-
Slight simplification of the last query:
SELECT
MIN(N.JustNumeric) AS MinID,
MAX(N.JustNumeric) AS MaxID,
F.[Fruit]
FROM
[#fruits] As F
CROSS APPLY ( SELECT substring(F.[ID], PATINDEX('%[0-9]%', F.[ID]), 1 + PATINDEX('%[0-9][^0-9]%', F.[ID] + 'x') - PATINDEX('%[0-9]%', F.[ID])) ) As N (JustNumeric)
GROUP BY
F.[Fruit]
;Still nasty, but at least the "computed column" doesn't have to be repeated. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
