12

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.

wootscootinboogie
  • 8,461
  • 33
  • 112
  • 197

4 Answers4

33

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
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 5
    I would upvote this answer ten times if it was possible. – AFract Oct 29 '15 at 09: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 01 '18 at 00:12
4

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
Pavel Veller
  • 6,085
  • 1
  • 26
  • 24
  • 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
-1

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.

Lady Serena Kitty
  • 59
  • 1
  • 8
  • it's `SELECT TOP 1 ... ` in MSSQL – Pavel Veller Jun 16 '12 at 15:01
  • 2
    @PavelVeller: But i assume that `TOP 1` is not what OP is looking for. He want just one record for _every_ different patientID. – Tim Schmelter 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: http://blogs.msdn.com/b/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx – Lady Serena Kitty Jun 16 '12 at 15:05
-1
WITH CTE AS
(
   SELECT tableName.*,ROW_NUMBER() OVER(PARTITION BY patientID ORDER BY patientID) As 'Position' FROM  tableName
)

SELECT * FROM CTE
WHERE
Position = 1
Gil Baggio
  • 13,019
  • 3
  • 48
  • 37