Flag unique values per field in SQL, 0 or 1

[Speshul]

I'm trying to create a field in an SQL query which will flag whether another field contains a unique value

Example, a query returns 1000 invoices. Some are duplicates, some are unique. I am trying to place a 1 on all uniques, and a 1 on only the first of the duplicates.

So

Please Login or Register  to view this content.

I'm at a loss!

Thank you

[LJMetzger]

Hi Speshul,

If I understand your question correctly, you have an Excel column of Invoice Numbers, and you want to identify the first occurrence of an Invoice Number and you want to identify duplicates.

This problem can be solved using an Excel 'Scripting Dictionary', which is very good at finding unique items in a list. For more information about the Excel 'Scripting Dictionary' see: http://www.snb-vba.eu/VBA_Dictionary_en.html

Try the attached file which contains the following code:

Please Login or Register  to view this content.

Lewis

[Speshul]

This is an interesting solution, but unfortunately it still crashes excel.

To clarify exactly what I am trying to do to hopefully narrow down possible solutions;

I have a data set which is pulled from an SQL query to be analyzed and pivoted within Excel. I have a column to determine the first unique occurrence of the invoice number which is a simple =IF(COUNTIF($B$2:B2,B2)=1,1,0)

Unfortunately, 90k records seems to be enough to completely lock up Excel every time the formula calculates or the sheet is re-sorted making it unusable. Even copy/paste special/values appears to lock up excel.


I am looking for a pre-excel solution, to do the equivalent calculation within the query itself as I have a feeling raw SQL has an easier time handling such calculation than excel.

[Kyle123]

This isn't possible ( I don't think) in vanilla SQL, most databases offer a superset of SQL though with additional functionality - so it depends on which database you are using as to how hard this is.

PS the reason that it's is that in simple terms, databases work within rows, not columns

[LJMetzger]

In the past, my technique has caused excel to crash at approximately 20,000 records due to Excel memory limitations for a module. If you want an Excel solution, it is possible that you may be successful with 64 bit Excel. Before buying, I would try 64 bit Excel out first. 64 bit Excel comes with compatibility issues (including code rewrite required) if API (Application Program Interface) functions are used in VBA.

Sorry I have no SQL experience. Even though your question was in the 'Access Tables and DataBases' forum, I thought you might be looking for an Excel Solution.

[Speshul]

I am working from Microsoft SQL Server Management Studio, database is Microsoft SQL Server Enterprise x64 (11.0)

Edit: LJMetzger, I am running 64bit Excel 2010 under Windows 7 Enterprise x64. I have tried =IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),0,1) which is faster but still quite slow for what I need


Flagging only the first occurrence seems like something that should exist, can't find it anywhere though :\

[Kyle123]

In TSQL you'd do it like this:

I've had to attach the file since the forum won't let me post sql

[Gregor y]

One of the problems is that data reccords are typically not stored sequentally within a database and tend to move arround as they get edited; so one that's first today may be last tomorrow. We'll need to know the sorting fields to help you build a query that looks for the "first" reccord...

MS-Access Example:

Field1	DateField
A	9/17/2015
A	9/18/2015
B	9/18/2015
C	9/18/2015
D	9/16/2015
D	9/17/2015
D	9/18/2015
E	9/18/2015
F	9/17/2015
F	9/18/2015

Note: Swap out e for é, in the following code

Please Login or Register  to view this content.

Gives:

Field1	DateField	CT	MinD	TST
A	9/17/2015	2	9/17/2015	1
A	9/18/2015	2	9/17/2015	0
B	9/18/2015	1	9/18/2015	1
C	9/18/2015	1	9/18/2015	1
D	9/16/2015	3	9/16/2015	1
D	9/17/2015	3	9/16/2015	0
D	9/18/2015	3	9/16/2015	0
E	9/18/2015	1	9/18/2015	1
F	9/17/2015	2	9/17/2015	1
F	9/18/2015	2	9/17/2015	0

[Gregor y]

oh shoot, I guess a couple more notes:

CT really wasn't needed in this case, as Field1+MinD forms a unique value on the table; however it does show how you can form a more complicated query criteria.

This query is going to run really CPU heavy on the server, and will get exponentially worse the more sub (sélect * from ...) statements you add per reccord count.

For your simple case you're much better off with Kyle123's solution as it uses built-in code to get better performance.

You can switch out IIF() from the MS-Acces code to the CASE statment when converting to a more standard SQL Code.