Select the first instance of a record
up vote
8
down vote
favorite
7
I have a table, myTable that has two fields in it ID and patientID. The same patientID can be in the table more than once with a different ID. How can I make sure that I get only ONE instance of every patientID.?
EDIT: I know this isn't perfect design, but I need to get some info out of the database and today and then fix it later.
sql sql-server-2008
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
add a comment |
up vote
8
down vote
favorite
7
I have a table, myTable that has two fields in it ID and patientID. The same patientID can be in the table more than once with a different ID. How can I make sure that I get only ONE instance of every patientID.?
EDIT: I know this isn't perfect design, but I need to get some info out of the database and today and then fix it later.
sql sql-server-2008
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
top 1only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.
– wootscootinboogie
Jun 16 '12 at 15:00
1
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05
add a comment |
up vote
8
down vote
favorite
7
up vote
8
down vote
favorite
7
7
I have a table, myTable that has two fields in it ID and patientID. The same patientID can be in the table more than once with a different ID. How can I make sure that I get only ONE instance of every patientID.?
EDIT: I know this isn't perfect design, but I need to get some info out of the database and today and then fix it later.
sql sql-server-2008
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
I have a table, myTable that has two fields in it ID and patientID. The same patientID can be in the table more than once with a different ID. How can I make sure that I get only ONE instance of every patientID.?
EDIT: I know this isn't perfect design, but I need to get some info out of the database and today and then fix it later.
sql sql-server-2008
sql sql-server-2008
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
asked Jun 16 '12 at 14:54
wootscootinboogie
3,3272781156
3,3272781156
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
top 1only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.
– wootscootinboogie
Jun 16 '12 at 15:00
1
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05
add a comment |
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
top 1only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.
– wootscootinboogie
Jun 16 '12 at 15:00
1
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
top 1 only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.– wootscootinboogie
Jun 16 '12 at 15:00
top 1 only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.– wootscootinboogie
Jun 16 '12 at 15:00
1
1
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05
add a comment |
3 Answers
3
active
oldest
votes
up vote
23
down vote
accepted
You could use a CTE with ROW_NUMBER function:
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
answered Jun 16 '12 at 14:58
Rango
359k45451712
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1
– Syed
Nov 1 at 0:12
add a comment |
up vote
3
down vote
It sounds like you're looking for DISTINCT:
SELECT DISTINCT patientID FROM myTable
you can get the same "effect" with GROUP BY:
SELECT patientID FROM myTable GROUP BY patientID
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.DISTINCTof onlypatientIDshould give you what you need. if you doDISTINCT patiendID, IDthen yes, you will get distinct combinations thus more than onepatientID
– Pavel Veller
Jun 16 '12 at 15:00
add a comment |
up vote
0
down vote
The simple way would be to add LIMIT 1 to the end of your query. This will ensure only a single row is returned in the result set.
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it'sSELECT TOP 1 ...in MSSQL
– Pavel Veller
Jun 16 '12 at 15:01
2
@PavelVeller: But i assume thatTOP 1is not what OP is looking for. He want just one record for every different patientID.
– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery usingROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…
– Lady Serena Kitty
Jun 16 '12 at 15:05
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
23
down vote
accepted
You could use a CTE with ROW_NUMBER function:
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
answered Jun 16 '12 at 14:58
Rango
359k45451712
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1
– Syed
Nov 1 at 0:12
add a comment |
up vote
23
down vote
accepted
You could use a CTE with ROW_NUMBER function:
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
answered Jun 16 '12 at 14:58
Rango
359k45451712
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1
– Syed
Nov 1 at 0:12
add a comment |
up vote
23
down vote
accepted
up vote
23
down vote
accepted
You could use a CTE with ROW_NUMBER function:
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
answered Jun 16 '12 at 14:58
Rango
359k45451712
You could use a CTE with ROW_NUMBER function:
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
answered Jun 16 '12 at 14:58
Rango
359k45451712
answered Jun 16 '12 at 14:58
Rango
359k45451712
answered Jun 16 '12 at 14:58
Rango
359k45451712
answered Jun 16 '12 at 14:58
Rango
359k45451712
359k45451712
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1
– Syed
Nov 1 at 0:12
add a comment |
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1
– Syed
Nov 1 at 0:12
4
4
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
I would upvote this answer ten times if it was possible.
– AFract
Oct 29 '15 at 9:34
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:
select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1– Syed
Nov 1 at 0:12
Good answer @TimSchmelter. The same output can also be achieved using a sub-query:
select * from ( SELECT myTable.* , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID) FROM myTable ) as myPatient where RN = 1– Syed
Nov 1 at 0:12
add a comment |
up vote
3
down vote
It sounds like you're looking for DISTINCT:
SELECT DISTINCT patientID FROM myTable
you can get the same "effect" with GROUP BY:
SELECT patientID FROM myTable GROUP BY patientID
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.DISTINCTof onlypatientIDshould give you what you need. if you doDISTINCT patiendID, IDthen yes, you will get distinct combinations thus more than onepatientID
– Pavel Veller
Jun 16 '12 at 15:00
add a comment |
up vote
3
down vote
It sounds like you're looking for DISTINCT:
SELECT DISTINCT patientID FROM myTable
you can get the same "effect" with GROUP BY:
SELECT patientID FROM myTable GROUP BY patientID
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.DISTINCTof onlypatientIDshould give you what you need. if you doDISTINCT patiendID, IDthen yes, you will get distinct combinations thus more than onepatientID
– Pavel Veller
Jun 16 '12 at 15:00
add a comment |
up vote
3
down vote
up vote
3
down vote
It sounds like you're looking for DISTINCT:
SELECT DISTINCT patientID FROM myTable
you can get the same "effect" with GROUP BY:
SELECT patientID FROM myTable GROUP BY patientID
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
It sounds like you're looking for DISTINCT:
SELECT DISTINCT patientID FROM myTable
you can get the same "effect" with GROUP BY:
SELECT patientID FROM myTable GROUP BY patientID
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
answered Jun 16 '12 at 14:56
Pavel Veller
5,28012023
5,28012023
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.DISTINCTof onlypatientIDshould give you what you need. if you doDISTINCT patiendID, IDthen yes, you will get distinct combinations thus more than onepatientID
– Pavel Veller
Jun 16 '12 at 15:00
add a comment |
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.DISTINCTof onlypatientIDshould give you what you need. if you doDISTINCT patiendID, IDthen yes, you will get distinct combinations thus more than onepatientID
– Pavel Veller
Jun 16 '12 at 15:00
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
Not exactly. The patient ID can show up in the table more than once with a diffierent tblclaims.id. So if i select distinct, that makes that I get that patientID returned more than once
– wootscootinboogie
Jun 16 '12 at 14:58
@wootscootinboogie only if you add more fields to the projections you're selecting.
DISTINCT of only patientID should give you what you need. if you do DISTINCT patiendID, ID then yes, you will get distinct combinations thus more than one patientID– Pavel Veller
Jun 16 '12 at 15:00
@wootscootinboogie only if you add more fields to the projections you're selecting.
DISTINCT of only patientID should give you what you need. if you do DISTINCT patiendID, ID then yes, you will get distinct combinations thus more than one patientID– Pavel Veller
Jun 16 '12 at 15:00
add a comment |
up vote
0
down vote
The simple way would be to add LIMIT 1 to the end of your query. This will ensure only a single row is returned in the result set.
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it'sSELECT TOP 1 ...in MSSQL
– Pavel Veller
Jun 16 '12 at 15:01
2
@PavelVeller: But i assume thatTOP 1is not what OP is looking for. He want just one record for every different patientID.
– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery usingROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…
– Lady Serena Kitty
Jun 16 '12 at 15:05
add a comment |
up vote
0
down vote
The simple way would be to add LIMIT 1 to the end of your query. This will ensure only a single row is returned in the result set.
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it'sSELECT TOP 1 ...in MSSQL
– Pavel Veller
Jun 16 '12 at 15:01
2
@PavelVeller: But i assume thatTOP 1is not what OP is looking for. He want just one record for every different patientID.
– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery usingROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…
– Lady Serena Kitty
Jun 16 '12 at 15:05
add a comment |
up vote
0
down vote
up vote
0
down vote
The simple way would be to add LIMIT 1 to the end of your query. This will ensure only a single row is returned in the result set.
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
The simple way would be to add LIMIT 1 to the end of your query. This will ensure only a single row is returned in the result set.
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
answered Jun 16 '12 at 14:55
Lady Serena Kitty
418
418
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it'sSELECT TOP 1 ...in MSSQL
– Pavel Veller
Jun 16 '12 at 15:01
2
@PavelVeller: But i assume thatTOP 1is not what OP is looking for. He want just one record for every different patientID.
– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery usingROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…
– Lady Serena Kitty
Jun 16 '12 at 15:05
add a comment |
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it'sSELECT TOP 1 ...in MSSQL
– Pavel Veller
Jun 16 '12 at 15:01
2
@PavelVeller: But i assume thatTOP 1is not what OP is looking for. He want just one record for every different patientID.
– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery usingROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…
– Lady Serena Kitty
Jun 16 '12 at 15:05
1
1
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
LIMIT is MySql, isn't it?
– Rango
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
yes, limit isn't in sql server
– wootscootinboogie
Jun 16 '12 at 14:59
it's
SELECT TOP 1 ... in MSSQL– Pavel Veller
Jun 16 '12 at 15:01
it's
SELECT TOP 1 ... in MSSQL– Pavel Veller
Jun 16 '12 at 15:01
2
2
@PavelVeller: But i assume that
TOP 1 is not what OP is looking for. He want just one record for every different patientID.– Rango
Jun 16 '12 at 15:02
@PavelVeller: But i assume that
TOP 1 is not what OP is looking for. He want just one record for every different patientID.– Rango
Jun 16 '12 at 15:02
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery using
ROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…– Lady Serena Kitty
Jun 16 '12 at 15:05
Okay, so LIMIT is available in MySQL and Drizzle, but doesn't SQL Server have something to similar effect? In fact it does, in a roundabout way. You can set up your query in a subquery using
ROW_NUMBER(), and then limit the selection using the generated row numbers. Source: blogs.msdn.com/b/sqlserver/archive/2006/10/25/…– Lady Serena Kitty
Jun 16 '12 at 15:05
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f11064473%2fselect-the-first-instance-of-a-record%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are there any criteria to decide which record to return or do you want to return any one at random?
– Panagiotis Kanavos
Jun 16 '12 at 14:57
top 1only returns one record. I need exactly one instance of every patient ID. It doesn't matter which tblClaims.id is left out.– wootscootinboogie
Jun 16 '12 at 15:00
1
Then solution using a CTE is the way to go
– Panagiotis Kanavos
Jun 16 '12 at 15:05