distinct wont work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

distinct wont work

Author

Topic

black_pearl
Starting Member

15 Posts

Posted - 2006-05-11 : 21:56:56

hello,

i have a table with 3 feilds. serial,date_time,code.
in the serial column, there are data that occur many times, but their date_time and code are different. i want to select only the rows which a particular serial occurs first. i tried to use this code, but it returns duplicate serial with different date_time and code.

select distinct * from table

i hope it isnt so confusing.

pls help.
thanks a lot.

black_pearl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 22:38:18

try
select distinct serial from table

black_pearl
Starting Member

15 Posts

Posted - 2006-05-11 : 22:45:54

yes, i have tried that, but i have to select all fields.
thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 22:49:04

maybe you can post your table structure, some sample data and the result that you want

black_pearl
Starting Member

15 Posts

Posted - 2006-05-11 : 23:02:28

ok here:

serial_____________datetime___________code

5U0604T01544| 2006-03-08 06:17:00| 3604.0
5U0604T01544| 2006-03-08 05:08:00| 0.0
5U0604T01559| 2006-03-08 07:05:00| 0.0
5U0604T01560| 2006-03-08 07:13:00| 0.0
5U0604T01561| 2006-03-08 07:20:00| 3607.0
5U0604T01561| 2006-03-08 07:14:00| 0.0
5U0604T01561| 2006-03-08 07:17:00| 0.0
5U0604T01562| 2006-03-08 07:16:00| 0.0
5U0604T01577| 2006-03-08 05:52:00| 3605.0
5U0604T01577| 2006-03-08 05:57:00| 3604.0
5U0604T01577| 2006-03-08 05:49:00| 3604.0

here are the result i want:

serial_____________datetime___________code
5U0604T01544| 2006-03-08 06:17:00| 3604.0
5U0604T01559| 2006-03-08 07:05:00| 0.0
5U0604T01560| 2006-03-08 07:13:00| 0.0
5U0604T01561| 2006-03-08 07:20:00| 3607.0
5U0604T01562| 2006-03-08 07:16:00| 0.0
5U0604T01577| 2006-03-08 05:52:00| 3605.0

...something like this.

thanks

__black_pearl__

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 23:20:39

How do you decide which records you want for the records with same serial ? Do take note that, records are not stored in any particular order in the table. So there isn't a first record or last record. You have to define the order of record to retrieve using ORDER BY.


5U0604T01544| 2006-03-08 06:17:00| 3604.0
5U0604T01544| 2006-03-08 05:08:00| 0.0

5U0604T01561| 2006-03-08 07:20:00| 3607.0
5U0604T01561| 2006-03-08 07:14:00| 0.0

5U0604T01577| 2006-03-08 05:52:00| 3605.0
5U0604T01577| 2006-03-08 05:57:00| 3604.0
5U0604T01577| 2006-03-08 05:49:00| 3604.0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 23:28:38

select *
from   table t
where  date_time = (select max(date_time) from table x where x.serial = t.serial)

black_pearl
Starting Member

15 Posts

Posted - 2006-05-12 : 00:55:01

thanks a lot! u are a real genius! mwah! mwah!

__black_pearl__

black_pearl
Starting Member

15 Posts

Posted - 2006-05-14 : 19:41:52

hello there,

regarding my current table, i have another query i need to do. how can i select those serials which have both zero and nonzero codes? pls help. thanks

__black_pearl__

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 01:56:38

. . . continue over at this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66048

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources