November 20, 2019 at 9:29 pm
i am struggling with a query to avoid while loop, please guide me if it can be done without a while loop.
--create temp table
create table #minmaxvalues
(
MaxVal sql_variant,
MinVal sql_variant,
percentval decimal(28,4)
)
-- insert values
insert into #minmaxvalues
select
15765,0,'1.0000'
union select 374922,15765,'1.0000'
union select 1178099,374922,'1.0000'
---using below query i need to get rowcount for each min and max value from tablea using values from #minmaxvalues. i have completed code by using while loop checking if there is a alternative solution
select pk , count(1)
from tablea
where pk > CAST(MinVal AS INT)
and pk < CAST(MaxVal AS INT)
group by pk
November 20, 2019 at 11:20 pm
You can solve this problem in the JOIN clause, by JOINing the tables #minmaxvals and tablea on a range:
/* matches original query, rows that match MinVal and MaxVal will not be included: */
SELECT ...
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk > mmv.MinVal and a.pk < mmv.MaxVal
/* this version includes the values in MinVal and MaxVal */
SELECT ...
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk BETWEEN mmv.MinVal and mmv.MaxVal
Once the JOIN clause is correct, the rest of the solution is easy, just throw COUNT and GROUP BY at the query:
SELECT mmv.minval, mmv.maxval, count(*)
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk BETWEEN mmv.MinVal and mmv.MaxVal
GROUP BY mmv.minval, mmv.maxval
ORDER BY mmv.minval, mmv.maxval
Here's the whole solution with sample tables. I had no reference for tablea, so I slapped one together in the demo script:
-- make this re-runnable
DROP TABLE IF EXISTS #minmaxvalues;
GO
DROP TABLE IF EXISTS #tablea;
GO
-- create a fake tablea, placing random numbers of rows for each 'pk' value
CREATE TABLE #tablea(pk int NOT NULL, val nvarchar(192) NOT NULL)
GO
WITH inputset(pk, val, RandomVal)
AS (
SELECT abs(object_id) AS pk, [name] AS val,
ABS(CHECKSUM(NEWID())) % 100 AS [RandomVal] -- generates a separate random value between 0-99 for each row
FROM sys.all_objects
)
-- [RandomVal is a random number between 0 and 99.
-- Filtering the rows WHERE RandomVal < 40 means 'grab 40% of the rows'
INSERT #tablea(pk, val)
SELECT pk, val FROM inputset WHERE RandomVal < 40 UNION ALL
SELECT pk, val FROM inputset WHERE RandomVal < 40 UNION ALL
SELECT pk, val FROM inputset WHERE RandomVal < 40 UNION ALL
SELECT pk, val FROM inputset WHERE RandomVal < 40 UNION ALL
SELECT pk, val FROM inputset WHERE RandomVal < 40 UNION ALL
SELECT pk, val FROM inputset WHERE RandomVal < 40;
GO
--create temp table
CREATE TABLE #minmaxvalues (
MaxVal sql_variant,
MinVal sql_variant,
percentval decimal(28,4))
-- insert values
insert into #minmaxvalues
select 15765,0,'1.0000'
union select 374922,15765,'1.0000'
union select 1178099,374922,'1.0000'
---using below query i need to get rowcount for each min and max value from tablea using values from #minmaxvalues. i have completed code by using while loop checking if there is a alternative solution
-- The solution drops tablea.pk from the SELECT and GROUP BY clauses,
-- counting the number of rows between the MinVal and MaxVal for each row in #minmaxvalues
SELECT mmv.minval, mmv.maxval, count(*)
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk BETWEEN mmv.MinVal and mmv.MaxVal
GROUP BY mmv.minval, mmv.maxval
ORDER BY mmv.minval, mmv.maxval
-- reference query #1 (view rows that will go into the count with the exclusive JOIN method)
-- this version matches your original query, and shows the specific rows that will be counted.
-- the min and max values from the table will NOT be included in the counts
-- (if MinVal = 100 and MaxVal = 1000, possible rows will be pk between 101 and 999)
SELECT mmv.minval, mmv.maxval, a.pk, count(*)
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk > mmv.MinVal and a.pk < mmv.MaxVal
GROUP BY mmv.minval, mmv.maxval, a.pk
ORDER BY mmv.minval, mmv.maxval, a.pk
-- reference query #2 (view rows that will go into the count with the inclusive JOIN method)
-- This version includes the values specified for MinVal and Max Val
-- (if MinVal = 100 and MaxVal = 1000, possible rows will be pk between 100 and 1000)
SELECT mmv.minval, mmv.maxval, a.pk, count(*)
FROM #minmaxvalues mmv INNER JOIN
#tablea a ON a.pk BETWEEN mmv.MinVal and mmv.MaxVal
GROUP BY mmv.minval, mmv.maxval, a.pk
ORDER BY mmv.minval, mmv.maxval, a.pk
-Eddie
Eddie Wuerch
MCM: SQL
November 21, 2019 at 11:04 pm
Never, never use that highly proprietary and totally nonrelational SQL_variant data type. We designed SQL to be a strongly typed language. The relational model depends on strong data typing. It's no wonder you think about a loop because you really have no idea how this was supposed to work. In fact, you don't even know that a table by definition, must have a key!
CREATE TABLE Foobar_Ranges
(foobar_range CHAR(5) NOT NULL PRIMARY KEY, --required!!
UNIQUE (foobar_val_min, foobar_val_max),
(foobar_val_min INTEGER NOT NULL
foobar_val_max INTEGER NOT NULL,
CHECK (foobar_val_min <= foobar_val_max));,
A percentage is usually a numeric value by definition. But you're putting it in as a string! Also, percentages are usually computed rather than being stored. So in your DDL and your data don't really make much sense.
Why did you use the old Sybase syntax for your insertion statement? It's been obsolete for decades.
INSERT INTO Foobar_Ranges
VALUES
('A', 0 , 15765),
('B', 15766 ,374922 ),
('C', 374923, 1178099);
>> ---using below query I need to get rowcount for each min and max value from Foobar_Ranges using values from foobar_ranges. <<
Your query makes no sense. You did not define a column named "PK",, and surely you know enough not to use metadata like "primary key" for column name. We would also never use COUNT(1) in SQL today; that was an old disaster from the first releases of Oracle. Why don't you just use an INTEGER data type instead of casting things to INTEGER Almost all the work in SQL is done in the DDL
SELECT foobar_range, COUNT(*) AS range_cnt
FROM Foobar_Ranges
WHERE foobar_range BETWEEN foobar_val_min AND foobar_val_max
GROUP BY foobar_range;
Now we have no rules about overlapping ranges, so I made up a rule that the ranges do not overlap. Almost all the work in SQL is done in the DDL. While it doesn't apply to SQL Server, other SQLs can do optimizations with a between predicate. It's a good habit to get into.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 22, 2019 at 3:23 am
Never, never use that highly proprietary and totally nonrelational SQL_variant data type.
You really need to come out of your shell on SQL, Joe. While I agree that the SQL_variant data type should usually be avoided and that it has probably been inappropriately used in this thread, there are some great uses for it. As for the highly proprietary stuff, yeah... you need to get over that, as well. The idea of easily migrated databases from one RDBMS Engine to another has never been anything other than a myth based on wishful thinking.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply