Min and Max or Range

Lost User

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.

realJSOP

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 #fruits

CREATE 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
-

Richard Deeming

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

sunsher

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.

realJSOP

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

realJSOP

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

Lost User

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.

Mycroft Holmes

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

realJSOP

:) 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

MadMyche

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

jsc42

You will also need something like

CONVERT(int, SUBSTRING(F.[ID], PATINDEX('%[0-9]%', F.[ID]), etc))

as otherwise, 10, 11,..., 19, 100, ..., 199 etc will fall between 1 and 2 because you are still comparing as strings