finding DISTINCT combinations

Hi,

I need to get 8 columns from a table.
1)
I must get only one entry of each unique combination of columsn 1,2,3.
2)
These entries must have the max. possible value in column 4.

Currently I have,

SELECT DISTINCT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO #temp
FROM TABLE1 A

INSERT INTO ANSWERTABLE
(one, two, three, four, five, six, seven, eight)

SELECT one, two, three, four, five, six, seven, eight
FROM #temp A
WHERE A.four = (SELECT MAX(B.four)
FROM TABLE B
WHERE B.one = A.two
AND B.two = A.two
AND B.three = A.three);my policy is, i don't answer what is obviously a homework problem unless you have made an attempt yourself, and in this case, it looks like you have

either i don't understand the problem, or else it's a lot simpler than you thinkselect one, two, three, max(four)
from yourtable
group by one, two, threeGROUP BY always yields distinct combinations

a good homework assignment makes you stop and think, doesn't it

"distinct" does not imply you have to use the DISTINCT keyword

rudy
http://r937.com/|||believe it or not, but this isn't a homework question. I just simplified the table and column names so that it would be easier to understand.

Thanks for your solution though.

But i need to be able to get entries from almost all 8 columns (ie. other columns which do not have to be distinct).|||Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()|||Originally posted by DoktorBlue
Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()

that's the thing, i don't want to use an aggregate function on those other columns.

The database has numerous entries for each unique combination of columns one, two and three. And I want the entire row for the entry that has the max value at column 4.

thanks for the help though.|||I want the entire row for the entry that has the max value at column 4. you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy|||Originally posted by r937
you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy

haha..
i think i did.

But i think I got it..check it out:

SELECT
one AS one,
two AS two,
three AS three,
max(four) AS four
INTO #temp
FROM TABLE1 A
GROUP BY one, two, three;

SELECT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO ANSWERTABLE
FROM TABLE1 A INNER JOIN #temp B
ON (A.one = B.one
AND A.two = B.two
AND A.three = B.three
AND A.four = B.four);

I'm checking the results...and it looks good so far.

See any bugs or loopholes ?

Thanks again.|||bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy|||Originally posted by r937
bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy

ya thanks...
got the same results (y)

but for some reason i can only get it to work if the "yourtable zz" is a temp table.
If i directly access the "yourtable" i get the following errors:

Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.|||did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy|||Originally posted by r937
did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy

ya i named the table...
it's funny, cause it's still a correlated query if I substitute the temp table for the outer query table.|||i cannot help you if you do not show me your query

rudy|||Originally posted by r937
i cannot help you if you do not show me your query

rudy
ya sorry...
i'm legally bound not to give out any code..
i know it's anal, and doesn't really matter and the code's not important or anything, but i still can't.

But it's exactly like how u posted (i'm pretty sure i can follow that, haha)
'cept that the 'yourtable' is a view.

I ran the code in MS Access, and it was able to run. Except it did not include only one unique combinations of columns one two and three.
So i guess I wouldn't be able to use it even if I got it to go in SQLServer

Finding and updating all rows with the same value in a column

This integer column should be unique, s? prior to altering it to unique i
need to traverse all rows and update the rows with the same value. This tabl
e
has another colun which is the primary key and when 2 rows has the same valu
e
the row with the highest value of the primary key will be updated.
Any easy T-SQL way to do it?
Regards,
Olavupdate your_table
set your_column = whatever you wish to set it to
where exists(select 1 from your_table yt
where your_table.your_column = yt.your_column
and your_table.PK_column > yt.PK_column)
Just curious: what kind of information do you have in that column so
that you can easily update it just to enforse uniqueness?sql

Finding a value within a range using IN

Patients may be assigned up to 4 diagnoses for an encounter. I need to selec
t
patients (by their unique ID) when any one of the 4 diagnoses falls within a
range. So I wrote the SELECT as below. Now I am curious if there is a more
concise syntax using IN. Here's what I did:
SELECT PatUniqueID FROM Charges WHERE
diag_code1 BETWEEN '300.00' AND '399.99' OR
diag_code2 BETWEEN '300.00' AND '399.99' OR
diag_code3 BETWEEN '300.00' AND '399.99' OR
diag_code4 BETWEEN '300.00' AND '399.99' ;
Thanks...The problem is that the design of table Changes is not normalized and SQL
statements are more concise when used with normalized data models. For
example, the following would be normalized:
PatUniqueID
VisitDate
diag_code
ChargeAmt
If the clinic decides that a patient may be assigned up to 5 diagnoses,
would this involve a re-write of the application?
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
> Patients may be assigned up to 4 diagnoses for an encounter. I need to
> select
> patients (by their unique ID) when any one of the 4 diagnoses falls within
> a
> range. So I wrote the SELECT as below. Now I am curious if there is a more
> concise syntax using IN. Here's what I did:
> SELECT PatUniqueID FROM Charges WHERE
> diag_code1 BETWEEN '300.00' AND '399.99' OR
> diag_code2 BETWEEN '300.00' AND '399.99' OR
> diag_code3 BETWEEN '300.00' AND '399.99' OR
> diag_code4 BETWEEN '300.00' AND '399.99' ;
> Thanks...
>|||JT wrote:

> If the clinic decides that a patient may be assigned up to 5
> diagnoses, would this involve a re-write of the application?
Indeed, a good rule:
If the customer even thinks there might be more than one, make it an
unlimited. It'll save you lots of trouble afterwards.
Kind regards,
Stijn Verrept.|||JT, I like that I often learn things from my posts that are not what I
thought I was asking about. In standard medical billing only up to 4
diagnoses are allowed (adding more is unlikely) and they must be rank
ordered. The primary diagnosis goes to a specific place in a claim, secondar
y
to another place, etc. In addition, the reference list of diagnoses changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the diagnoses?
"JT" wrote:

> The problem is that the design of table Changes is not normalized and SQL
> statements are more concise when used with normalized data models. For
> example, the following would be normalized:
> PatUniqueID
> VisitDate
> diag_code
> ChargeAmt
> If the clinic decides that a patient may be assigned up to 5 diagnoses,
> would this involve a re-write of the application?
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
>
>|||>> In standard medical billing only up to 4 diagnoses are allowed (adding mo
re is unlikely) and they must be rank ordered. <<
Modifying the DDL you never posted, try something like this:
CREATE TABLE MedForms
(patient_id CHAR(20) NOT NULL,
diag_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
icd_1 CHAR(7) NOT NULL CHECK (icd_1 LIKE
'[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_2 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_3 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_4 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
. );
I assume that you are using the ICD codes. You might also want to add
a REFERENCES clause to a table.
The primary diagnosis goes to a specific place in a claim, secondary
to another place, etc. In addition, the reference list of diagnoses
changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless
the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the
diagnoses?|||Yes, I would still normalize the diagnosis codes in a seperate table, but I
come from a data warehousing background where retaining historical,
versioned, and even obsolete data is one of the primary requirements of the
database. The Charges table could have a column indicating it's rank.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FC270CD0-F38E-4E3C-ADD1-B0931A692FA5@.microsoft.com...
> JT, I like that I often learn things from my posts that are not what I
> thought I was asking about. In standard medical billing only up to 4
> diagnoses are allowed (adding more is unlikely) and they must be rank
> ordered. The primary diagnosis goes to a specific place in a claim,
> secondary
> to another place, etc. In addition, the reference list of diagnoses
> changes.
> A 2005 diagnosis might be absent from the 2006 reference list. Unless the
> dx
> is actually written to the claim, obsolete references would have to be
> maintained forever. Given all that would you still normalize the
> diagnoses?
> "JT" wrote:
>|||I work in a hospital and the dx codes are ordered in a separate table.
However, the issue with your query is that your where clause looks like
you are filtering a numeric field when you actually have a varchar
datatype. Between 300.00 and 399.99 is 33.0. I use something like the
following:
SELECT PatUniqueID FROM Charges WHERE
case when isnumeric(diag_code1) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code2) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code3) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code4) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 .
Then case statement is necessary because some dx codes contain alpha
characters.
Alternatively, you could create a view of the table that contains the
the numeric dx code as well as the varchar code. I don't like this
option as much because you have to translate the columns even when they
are not needed.sql

Finding a user that has multiple log-ins at the same time

I have a table that contains the following

UserName - Which is unique to each user

TimeStart - Which is the time and date that the user logs in

TimeEnd - Which is the time and date that the user logs out

Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.

Try this query....

Select
MainQ.*
From
UserLog MainQ
Join
(
Select

Main.UserName,
Main.TimeStart,
Main.TimeEnd
From
UserLog Main
Join UserLog Started On Main.UserName = Started.UserName
And Main.TimeStart <= Started.TimeStart
And Main.TimeEnd >= Started.TimeStart

Group BY
Main.UserName,
Main.TimeStart,
Main.TimeEnd
Having Count(*) <> 1
) as Data
On MainQ.UserName = Data.UserName
And MainQ.TimeStart >= Data.TimeStart
And MainQ.TimeStart <= Data.TimeEnd

find unique identifier through multiple tables

>In one table there is GUID as a primary key and another GUID as
foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<

I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.

This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

You really need to throw this thing out. But if you cannot, then
update your resume.--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In one table there is GUID as a primary key and another GUID as

foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<
>
I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.
>
This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

He can add REFERENCES clauses (once he figures out which ones should
be added, and cleans up any existing exceptions), surely?|||>He can add REFERENCES clauses (once he figures out which ones should be added, and cleans up any existing exceptions), surely? <<

Then he will have mimicked a 1970's pointer chain DB in SQL instead of
making this a properly designed RDBMS.

For example, if I use an VIN for an automobile, I can verify the VIN
by going to the automobile, the DMV, insurance company, etc. But if I
use a GUID (or any other hardware generated value), I have no trusted
external source for verification.

I do not have a good way to validate it, in fact. The magical
universal GUID might be used for an automobile, a squid or Britney
Spears!

find unique

hey all,
i have a table which i would like to determine what fields make each record
unique? Unfortunately there is not a tableid.
thanks,
rodchar>> i have a table which i would like to determine what fields [sic] make each record [sic]
unique? Unfortunately there is not a tableid [sic]. <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. This is a pain to do after the
fact.
1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
"query to a working table. This kindof crappy non-table probably has
accumulated garbage.
2) Pick the columns that should be keys from the data model. The data
model which you probably do not have.
3) Test this subset of columns with
SELECT 'Bad Choice'
FROM Foobar
GROUP BY <<list of columns>>
HAVING COUNT(*) > 1;
4) reduce the number of test columns when you get a winner.|||what's the [sic] mean?
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. This is a pain to do after the
> fact.
> 1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
> "query to a working table. This kindof crappy non-table probably has
> accumulated garbage.
> 2) Pick the columns that should be keys from the data model. The data
> model which you probably do not have.
> 3) Test this subset of columns with
> SELECT 'Bad Choice'
> FROM Foobar
> GROUP BY <<list of columns>>
> HAVING COUNT(*) > 1;
> 4) reduce the number of test columns when you get a winner.
>|||rodchar wrote:
> what's the [sic] mean?
http://www.freesearch.co.uk/dictionary/sic
David Gugick
Quest Software
www.imceda.com
www.quest.com

Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-Ash
Try,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA
|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:
> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> > I understand this might be trivial but;
> > What's a neat query to find if two fields together are unique and can be
> > used as a PK ?
> > Thanks,
> > -Ash
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash