CODEWITHSUNDEEP
sqlsql-server-2008
AFJ
AFJ

Reputation: 25

SQL Query Returns different results based on the number of columns selected

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

Upvotes: 0

Views: 655

Answers (2)

Martin Smith
Martin Smith

Reputation: 453898

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

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

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?

Upvotes: 0

Related Questions