unique row id
-
Hello,
I have a table but someone requested to put row id's on each row, which this will be dynamic, so here is what data i got below:
CREATE TABLE customerdata
(
customerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerLastName varchar(100),
Balance int,
);
INSERT INTO customerdata (customername, customerlastname, balance)
VALUES ('First','dude', 500);
INSERT INTO customerdata (customername, customerlastname, balance)
VALUES ('Second','sam',250);
INSERT INTO customerdata (customername, customerlastname, balance)
VALUES ('Third','bob', 100);
INSERT INTO customerdata (customername, customerlastname, balance)
VALUES ('Fourth','kid', 755);
INSERT INTO customerdata (customername, customerlastname, balance)
VALUES ('Fifth','billy', 75);
here is the sample table above, what they want is to do something like:
select rowid, customerid, customername, customerlastname, balance
order by balance desc
so it shows who owes the most which will be the top and to the one who owes the least. but as you can see on the select statement, they want row id, which again this will be dynamic, so the table might change later on, either adding, editing or removing new rows from the table. the question is, how can i organize this, when i try to do it, it shows multiple rows of the same data even when i use cte, because when i do order by balance and use row_number(), then it creates multiple rows. what else can i do? here is an example of what they would like:
rowid customerid customername customerlastname balance
1 4 fourth kid 755
2 1 first dude 500
3 2 second same 250
4 3 third bob 100
5 5 fifth billy 50
thanks in advance
-
First of all, you needed to add the IDENTITY keyword to your customerID field, as it is a primary field and when you run your INSERTs it tries to INSERT NULL into that field which is not allowed.
Try this:
CREATE TABLE #customerdata
(
customerID INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerLastName varchar(100),
Balance int,
);
INSERT INTO #customerdata (customername, customerlastname, balance)
VALUES ('First','dude', 500);
INSERT INTO #customerdata (customername, customerlastname, balance)
VALUES ('Second','sam',250);
INSERT INTO #customerdata (customername, customerlastname, balance)
VALUES ('Third','bob', 100);
INSERT INTO #customerdata (customername, customerlastname, balance)
VALUES ('Fourth','kid', 755);
INSERT INTO #customerdata (customername, customerlastname, balance)
VALUES ('Fifth','billy', 75);
SELECT rowid=ROW_NUMBER() OVER (ORDER BY balance DESC)
,customerID, CustomerName, CustomerLastName, Balance
FROM #customerdata
DROP TABLE #customerdata
Unless I'm misunderstanding your requirement, this should do it for you.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
The only thing I would add is that you might be looking for RANK or DENSE_RANK instead of ROW_NUMBER, depending on the specs.
-
Hello everyone
thanks dwain for the correction, however I have tried what you have and came up with the same results for example:
if i do the rowid = row_Number() etc., but also I must have order by Balance desc, which if i add that into the query string, then i get:
rowid customerid customername customerlastname balance
1 4 fourth kid 755
1 4 fourth kid 755
2 1 first dude 500
3 2 second sam 250
3 2 second sam 250
4 3 third bob 100
4 3 third bob 100
5 5 fifth billy 50
Also i double check the tables and data, there is only 1 single data row for each customer, no duplicates. any other suggestions i am open to anything.
I have tried to rank, dense_rank and sadly doesnt work 🙁
-
Siten0308 (6/19/2012)
Hello everyonethanks dwain for the correction, however I have tried what you have and came up with the same results for example:
if i do the rowid = row_Number() etc., but also I must have order by Balance desc, which if i add that into the query string, then i get:
rowid customerid customername customerlastname balance
1 4 fourth kid 755
1 4 fourth kid 755
2 1 first dude 500
3 2 second sam 250
3 2 second sam 250
4 3 third bob 100
4 3 third bob 100
5 5 fifth billy 50
Also i double check the tables and data, there is only 1 single data row for each customer, no duplicates. any other suggestions i am open to anything.
I have tried to rank, dense_rank and sadly doesnt work 🙁
I have no idea how you'd get duplicated results out of the SQL I posted. Can you post DDL, sample data and the query you're using to arrive at the results above?
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Hello Dwain,
maybe you can help me answer my other question, which might solve the problem, i am going to make a temp table, then insert the records I want from variables tables, then on the particular column that needs the row number, I was going to just make an the column increment by 1, however, how would i go about doing that?
-
Siten0308 (6/19/2012)
Hello Dwain,maybe you can help me answer my other question, which might solve the problem, i am going to make a temp table, then insert the records I want from variables tables, then on the particular column that needs the row number, I was going to just make an the column increment by 1, however, how would i go about doing that?
The easiest way to do this is to add an identity column as Dwain suggested before. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Siten0308 (6/19/2012)
Also i double check the table[font="Arial Black"]s[/font]You simply have a join that's producing a 1 to many relationsip. You need to fix that if you want single returns. You could use DISTINCT or GROUP BY but make sure you're not just covering up a bad join.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
sorry all, my fault, yes i definitly want to say dwain solved the problem, when i did it, i was doing it on the same table X(, again my fault, when i did it right using the query, everything came back beautiful,
Want to say thank you and its been solved with the help of dwain
-
Siten0308 (6/21/2012)
sorry all, my fault, yes i definitly want to say dwain solved the problem, when i did it, i was doing it on the same table X(, again my fault, when i did it right using the query, everything came back beautiful,Want to say thank you and its been solved with the help of dwain
Most happy to be of service to you!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply