0

Hello I am writing a query and am little confused about the results i'm getting.

select distinct(serial_number) from AssyQC

This query returns 309,822 results

However if I modify the select statement to include a different column as follows

select distinct(serial_number), SCAN_TIME from AssyQC

The query returns 309,827 results. The more columns I add the more results show up.

I thought the results would be bound to only the distinct serial_number that were returned initially. That is what I want, only the distinct serial_numbers

Can anyone explain this behavior to me?

Thanks

AFJ
  • 25
  • 3
  • possible duplicate of [SQL - Select distinct but return all columns ???](http://stackoverflow.com/questions/6127338/sql-select-distinct-but-return-all-columns) – Denis de Bernardy May 25 '11 at 16:38

2 Answers2

1

SELECT distinct applies to the whole selected column list not just serial_number.

The more columns you add then clearly the more unique combinations you are getting.

Edit

From your comment on Cade's answer

let's say i wanted the largest/latest time stamp

this is what you neeed.

SELECT serial_number, MAX(SCAN_TIME) AS SCAN_TIME
FROM AssyQC
GROUP BY serial_number

Or if you want additional columns

;WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY serial_number 
                              ORDER BY SCAN_TIME DESC) AS RN
FROM AssyQC
)
SELECT *
FROM CTE 
WHERE RN=1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I'm not sure I follow, can you elaborate a little bit? – AFJ May 25 '11 at 16:38
  • It looks at the values in all the selected columns when removing duplicates. Putting parentheses in the query has no effect. – Martin Smith May 25 '11 at 16:43
  • OK, I understand now. I guess I assumed since I typed it such as distinct(serial_number) with the parenthesis it would only apply to that serial number column – AFJ May 25 '11 at 16:45
  • @AFJ - So what did you want to be returned for the other columns? Presumably 5 of the `serial_number` values have more than one `SCAN_TIME` from the numbers in your question (or possibly 1 might have 5 `scan_times` etc) – Martin Smith May 25 '11 at 16:46
0

you're probably looking for

select distinct on serial_number serial_number, SCAN_TIME from AssyQC

See this related question:

SQL/mysql - Select distinct/UNIQUE but return all columns?

Community
  • 1
  • 1
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154