Most tricky
-
Anyone know a way of getting sequential numbering of duplicates
in a table with no primary key.
so
create table tab (values varchar(100))
insert tab
select 'val' union all
select 'val' union all
select 'val' union all
select 'val' union all
select 'val'
without using a temp table or a cursor/while i want a select statement that returns
1, val
2, val
3, val
4, val
5, val
I understand that this is totally wrong design. But i need a way to do this as part of a wider problem.
Thanks very much.
-
I had posted this in your other post in the 2005 forum just before you moved it:
I would think you would just want to add an identity column and then specify the columns you want to insert as follows:
create table tab (id INT IDENTITY(1,1), [values] varchar(100))
insert into tab
( [values] )
select 'val' union all
select 'val' union all
select 'val' union all
select 'val' union all
select 'val'
SELECT * From tab
-
Do you mean like this
SELECT a.[value],n.number
FROM
a
INNER JOIN (SELECT [value],COUNT(*) AS [Num]
FROM
GROUP BY [value]) s
ON s.[value] = a.[value]
INNER JOIN master.dbo.spt_values n ON n.type = 'p' AND n.number BETWEEN 1 AND s.Num
GROUP BY a.[value],n.number
ORDER BY a.[value],n.number
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David ,
Above query u given is working only in case of a table contain all duplicate records..
But if there are some records which are disstinct it will not work...
I have slightly modified the logic which u have supplied...
Try this query....
SELECT * FROM (
SELECT a.[name],n.number
FROM [tab_b] a
INNER JOIN (SELECT [name],COUNT(*) NUM
FROM [tab_b] GROUP BY [name] HAVING COUNT(*) > 1) s
ON s.[name] = a.[name]
INNER JOIN master.dbo.spt_values n ON n.type = 'p' AND n.number BETWEEN 1 AND
S.NUM
GROUP BY a.[name],n.number )A
UNION ALL
SELECT A.NAME ,
(SELECT MAX(A.number ) FROM (
SELECT a.[name],n.number
FROM [tab_b] a
INNER JOIN (SELECT [name],COUNT(*) NUM
FROM [tab_b] GROUP BY [name] HAVING COUNT(*) > 1) s
ON s.[name] = a.[name]
INNER JOIN master.dbo.spt_values n ON n.type = 'p' AND n.number BETWEEN 1 AND
S.NUM
GROUP BY a.[name],n.number )A) + COUNT(*) NUM
FROM(
SELECT NAME , COUNT(*) B
FROM TAB_B
GROUP BY NAME
HAVING COUNT(*) =1)A
CROSS JOIN
(SELECT B.NAME FROM(
SELECT NAME , COUNT(*) BC
FROM TAB_B
GROUP BY NAME
HAVING COUNT(*) =1)B)B
WHERE a.name >= b.name
group by a.name
This query will work in all cases.........
Regards ,
Amit Gupta..
-
I disagree. I think David's solution works in all cases:
create table
([value] varchar(100))
insert into
([value])
select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val2'
union all select 'Val2'
union all select 'Val2'
union all select 'Val3'
union all select 'Val4'
union all select 'Val5'
union all select 'Val5'
union all select 'Val5'
select * from
SELECT n.number
,a.[value]
FROM
a
INNER JOIN (
SELECT [value],COUNT(*) AS [Num]
FROM
GROUP BY [value]) s
ON s.[value] = a.[value]
INNER JOIN master.dbo.spt_values n ON n.type = 'p' AND n.number BETWEEN 1 AND s.Num
GROUP BY a.[value],n.number
ORDER BY a.[value],n.number
-
better solution (one less join 
)SELECT a.[value],n.number
FROM (SELECT [value],COUNT(*) AS [Num] FROM
GROUP BY [value]) a
INNER JOIN master.dbo.spt_values n ON n.type = 'p' AND n.number BETWEEN 1 AND a.Num
ORDER BY a.[value],n.number
Far away is close at hand in the images of elsewhere.
Anon. -
I know this is a SQL 2000 forum, but here's the 2005 solution....
CREATE TABLE #tmp (yourField varchar(100))
INSERT INTO #tmp (yourField)
select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val1'
union all select 'Val2'
union all select 'Val2'
union all select 'Val2'
union all select 'Val3'
union all select 'Val4'
union all select 'Val5'
union all select 'Val5'
union all select 'Val5'
SELECT
row_number() OVER (PARTITION BY yourField ORDER BY yourField) AS rn
,yourField
FROM #tmp
DROP TABLE #tmp
All the more reason to look into '05 ...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Because, first, you must have an envelope
Lot's of folks just aren't gonna make it to 2005 because of budget and manpower. 
--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)
Intro to Tally Tables and Functions -
Jeff,
*laughs* ....
Yes, but this was posted in the 2005 forum as well so I had to submit it here as well. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply