SQL Server Performance Forum – Threads Archive

why not distinct work properly

I have table which has one datetime field as primary key.This table contains
some rows which has same value for datetime when i try to insert data into
other table by selecting from this table to another table .It give error
of primary key violation bcoz this datetime field is primary key in
other table .i also use distinct but it return rows which has same value for
datetime field
Actually i make some test cases
My query is this
insert into [‘vehicle_history_gsrtc-11’]
SELECT distinct top 100 [vehicle_id], [vehicle_sms_no], [vehicle_lattitude], [vehicle_longitude], [vehicle_off_lattitude], [vehicle_speed], [vehicle_off_longitude], [vehicle_key_status], [vehicle_inputpin_status], [vehicle_outputpin_status], [vehicle_datetime], convert(datetime,[vehicle_gpsdatetime],101) as vehicle_gpsdatetime, [vehicle_direction], [vehicle_string], [vehicle_location], [sms_datetime], [vehicle_message_type], [vehicle_distance], [vehicle_location_mapobjects], [vehicle_gpsdate], [vehicle_gpstime], [sms_datacall_record_ind], [report_message_type], [record_enable_ind] FROM [Fleetvigil_Web].[dbo].[vehicle_history]
where vehicle_id=’gsrtc-11′

Distinct will remove duplicate rows only if there are identical rows. Post the result that are returned by the query Madhivanan Failing to plan is Planning to fail

i am curious..why do you use datetime as the primary key of your table?

Why not? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Btw, DATETIME values are "only" unique down to 3,33 milliseconds. Since you can insert one than 1 row within a timespan of 3,33 millisecond, it is likely that the current DATETIME value hasn’t changed since the last INSERT. So, you a heavily inserted table choosing a DATETIME column as PRIMARY KEY might turn out not to be the best choice around. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

in my application it is the most appropriate filed for primary key
.Madhivanan reply has clear my doubt .There is one field which has different
field .now i change my constraint

Problem solved? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

how i make sure that that only those rows are selected which has unique value
in one field

So you have some distinct rows in vehicle_history table that have the same vehicle_dateTime value. Before answering your question we need to know the criteria what values you want to be returned for other columns when two rows have the same value for the vehicle_dateTime column.

]]>