Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

TSQL Best practice No loop

I have a table that has the fields: sCustID and severl other fields.. I want to "Group" together the like sCustid's by giving them a unique GroupID number.. If you run my code you will see what I'm trying to do.. The only problem is my code is backwards.. It groups the ones that are no alike together and the ones that are alike have different group numbers.. What am I doing wrong?
DECLARE @tmp TABLE (id TINYINT IDENTITY(1,1), sCustID CHAR(10)) 
 
INSERT @tmp

SELECT sCustID FROM ctsapp.app.tblVRUInvReq
 
	SELECT  
    o.sCustID, o.id - omin.minid + 1 AS GroupNum 
		FROM @tmp o 
		INNER JOIN 
			(SELECT sCustID, MIN(id) minid  FROM @tmp GROUP BY sCustID) AS omin ON 
                o.sCustID = omin.sCustID

Open in new window

0
cheryl9063
Asked:
cheryl9063
  • 3
  • 3
1 Solution
 
ralmadaCommented:
can you try using the dense rank function?
SELECT sCustID, dense_rank() over (order by sCustID) as groupno FROM ctsapp.app.tblVRUInvReq

Open in new window

0
 
GDG_DBACommented:
I am not sure what you are trying to do with this part:

o.id - omin.minid + 1 AS GroupNum


Try just using the omin.minid.  (See attached)
-G
SELECT  
    o.sCustID, omin.minid AS GroupNum --, o.id - omin.minid + 1 AS GroupNum 
		FROM @tmp o 
		INNER JOIN 
			(
			SELECT sCustID, MIN(id) minid  FROM @tmp GROUP BY sCustID
			) AS omin ON 
                o.sCustID = omin.sCustID

Open in new window

0
 
cheryl9063Author Commented:
I don't know what I was doing either but thanks!!
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
ralmadaCommented:
Hmmm, have you tried my suggestion?
0
 
cheryl9063Author Commented:
Although I'm sure your answer is fine I don't want to use your suggestion..Based on  many reasons  I need to use the query I was using.. My question was what was wrong with my code not how do you perform the same thing using different approaches.. Also, my DBA does not want more functions at this time but many many thanks for your response.
0
 
ralmadaCommented:
>>I don't want to use your suggestion..Based on  many reasons  I need to use the query I was using.. My question was what was wrong with my code not how do you perform the same thing using different approaches.. <<
your current approach is very clunky. There's no need to create a new temporary table and then select assign group id.the way you're doing

>>Also, my DBA does not want more functions at this time but many many thanks for your response. <<
You don't have to create any function here. Dense_rank is a function that comes with SQL server, same as MIN, MAX, etc.

If you still insist, it's up to you, all I'm saying that at least you should have tried my suggestion and split the points accordingly since I have provided you a valid solution, waaaay simpler than the one you currently have.

0
 
cheryl9063Author Commented:
If you run an execution plan and see what the Optimzer prefers
Query 1 ( my way)  cost 21%

Query 2( your way) cost 56%

Any kind of function results in a productivity impact.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now