473,231 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Unique ID added to a result set

Hello all, i have a question i've beenk thinking for a little bit and
cant seem to come up with a solution. What i want to do is add a unique
counter to a resultset in query. For example

SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM AnyCombinationOfJoinsOrTables
WHERE SomeConditionIsTrue

The query above will produce a resultest of 4 columns.

'A1', 'B1', 'C1', 'D1'
'A2', 'B2', 'C2', 'D2'
'A3', 'B3', 'C3', 'D3'
'An', 'Bn', 'Cn, 'Dn'

Now what i want to do is add a unique identifier to every row. The
easiest way would be to add a counter integer so the resultset would
look like

1, 'A1', 'B1', 'C1', 'D1'
2, 'A2', 'B2', 'C2', 'D2'
3, 'A3', 'B3', 'C3', 'D3'

n, 'An', 'Bn', 'Cn, 'Dn'
Is there a way to achieve this in T-SQL without using cursors.



Oct 5 '05 #1
3 1693
Best would be to do it client side. The numbering implies some kind of
order but there is no ORDER BY clause in your example query. Here's an

FROM pubs.dbo.authors
WHERE au_id <= A.au_id) AS row_num,
FROM pubs.dbo.authors AS A
ORDER BY au_id ;

David Portas
SQL Server MVP

Oct 5 '05 #2
David, thank you for the response. That is pretty slick and works for
my example however i did not specify that there is a chance that there
is no unique Fielld such as AU_ID that you can do comparison to do the
what i'm trying to do is to come up programmatically in SQL with a
standard way to add a counter (Unique Integer Value) to a resultset
with any number of tables and rows.

Is there a way to achieve this in the absence of a unique value such as
aui_ID in your example?

Thanks again.


Oct 5 '05 #3
I would have to ask why you want to return the duplicate data in the
first place. I originally assumed that this was just for display on the
screen, in which case the client application is surely the best place
to solve the problem. Duplicate data in the database is bad news as I'm
sure you know and adding an arbitrary integer value obviously doesn't
solve the problem of redundancy, except maybe in the case of an
intermediate "staging" table. In a staging table you would probably
want to use IDENTITY to generate the row numbers and then filter the
result based on some logical key.

In SQL 2000 there is no generic method to generate row numbers in a
query. In SQL Server 2005 we have the ROW_NUMBER function to do this.

David Portas
SQL Server MVP

Oct 5 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: les_ander | last post by:
Hi, I have many set objects some of which can contain same group of object while others can be subset of the other. Given a list of sets, I need to get a list of unique sets such that non of the...
by: Rolf Kemper | last post by:
Dear Experts, I got stuck with the following problem and need your help. What I wnat to do is to get a set of distinct nodes. Before the distinct I have selected the multiple occourences...
by: Joanna Epstein | last post by:
I have several tables formatted as follows: ITEM #, ITEM DESCRIPTION, PL, JANUARY ITEM #, ITEM DESCRIPTION, PL, FEBRUARY And so on… I need to create a final report that merges all of the...
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and selecting Primary Key. But I've heard that's not a...
by: Brian Keating | last post by:
Hi there, Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...? i.e....
by: deko | last post by:
Is there an accepted or standard way to get a unique name given a string and the collection in which it needs to be unique? Should I use a HashTable? Other options? Here's a first crack: ...
by: fong.yang | last post by:
I have tried to use the query wizard to find unmatched records but it doesn't seem to be going right. The results are still giving me some records that are in both tables. I have two tables...
by: deaddog4201 | last post by:
How can i save a .txt file where the name of the file would be the result of the selection made from a Dom tree view's parent node tag in visual basic? So for example i have an editable tree view...
by: Mystagogue | last post by:
Given XML like this... <stuff> <thing id="1" result="true"/> <thing id="2" result="true"/> <thing id="3" result="false"/> <thing id="4" result="false"/> <thing id="5" result="error"/>...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.