Solved

TSQL Best practice No loop

Posted on 2010-11-15
7
442 Views
Last Modified: 2012-05-10
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
Comment
Question by:cheryl9063
  • 3
  • 3
7 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 34140115
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
 
LVL 10

Accepted Solution

by:
GDG_DBA earned 500 total points
ID: 34140164
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
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 34140199
I don't know what I was doing either but thanks!!
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 41

Expert Comment

by:ralmada
ID: 34140227
Hmmm, have you tried my suggestion?
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 34140273
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
 
LVL 41

Expert Comment

by:ralmada
ID: 34140359
>>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
 
LVL 1

Author Comment

by:cheryl9063
ID: 34140528
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now