Distinct rows - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Distinct rows

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 06:09:46

I have a statement as follows

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time from delivery where sign = '' order by con_num, gmt_eve_date desc, gmt_eve_time desc

its works fine and return about 6 or 7 lines per con_num ordered by date

747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 0715
747407167 NVM/GB/SWE2 MMA 20041119 EXCEPTION 20041122 1055
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041122 0444
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041121 1013
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041120 1556
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041120 1556
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2349
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2053
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 1922
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 1339
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 1121
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0903
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0325
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0123
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041218 0450
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041217 2016
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041217 1540

This is an example of two different con_num. What I want to do is to onlu use the first row (the most recent one) for each con_num. How do I do it so that the result gives the following

747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 0715
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 1339

I obviously can't use distinct as the values are different and I can't use top as that returns only the number of rows specified

any help would be greatly appreciated

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 06:22:13

Didnt test it though...something on these lines could get what you want.

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time from delivery where sign = ''
and con_num in (
select min(con_num)
from delivery
group by con_num
having gmt_eve_date=max(gmt_eve_date))

order by con_num,
gmt_eve_date desc, gmt_eve_time desc

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 06:24:41

Thanks for the reply unfortunately I get the following error : -

Column 'delivery.gmt_eve_date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-07 : 06:26:37

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time from delivery
where sign = '' and gmt_eve_time in (select max(gmt_eve_time) from delivery group by con_num)
order by con_num, gmt_eve_date desc, gmt_eve_time desc

Madhivanan

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 06:28:10

I don't get any errors but I get the following results

747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2349
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2053
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 1922
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041217 2016

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-07 : 06:31:59

Try this

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time from delivery
where sign = '' and gmt_eve_date in (select max(gmt_eve_date) from delivery group by con_num)
order by con_num, gmt_eve_date desc, gmt_eve_time desc

Madhivanan

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 06:41:26

sorry, this is a little worse I get this result now

747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 0715
747407167 NVM/GB/SWE2 MMA 20041119 EXCEPTION 20041122 1055
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041122 0444
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2349
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 2053
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041119 1922
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 1339
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 1121
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0903
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0325
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 0123
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041217 2016
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041217 1540

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 06:43:11

oops! lemme try again.

but whenever you ask a question ,please proved DDL and DML statemnets.

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time
from delivery d inner join
(select con_num,max(gmt_eve_date) ged
from delivery
group by con_num) p
on p.con_num=p.con_num and d.gmt_eve_date=p.ged
where sign = ''
order by con_num,
gmt_eve_date desc, gmt_eve_time desc

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 06:54:56

Sorry that has given over 65000 records and there are only 7400 in the database!!!

I'm happy to provide DDL and DML but alas I don't actually know what they are :D

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:01:41

My Bad!!!

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time
from delivery d inner join
(select con_num,max(gmt_eve_date) ged
from delivery
group by con_num) p
on d.con_num=p.con_num and d.gmt_eve_date=p.ged
where sign = ''
order by con_num,
gmt_eve_date desc, gmt_eve_time desc

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-07 : 07:02:52

What are the data type of gmt_eve_date and gmt_eve_time?

Madhivanan

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:08:18

select con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time
from delivery d inner join
(select con_num,max(gmt_eve_date) ged,max(gmt_eve_time) gdt
from delivery
group by con_num) p
on d.con_num=p.con_num and d.gmt_eve_date=p.ged and gmt_eve_time=gdt
where sign = ''
order by con_num,
gmt_eve_date desc, gmt_eve_time desc

DDL statements are Data Definition Language Statement.
eg: Create table Delivery(con_num numeric(10)......)

DML Statements are Data Definition Language.
eg: insert into delivery values(......)

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 07:08:30

Server: Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'con_num'.

data type of gmt_eve_date and gmt_eve_time are varchar and I have no real control over type as the information is imported via textfile in the format 20050201

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:21:38

my gosh! am slipping

select d.con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time
from delivery d inner join
(select con_num,max(gmt_eve_date) ged,max(gmt_eve_time) gdt
from delivery
group by con_num) p
on d.con_num=p.con_num and d.gmt_eve_date=p.ged and gmt_eve_time=gdt
where sign = ''
order by d.con_num,
gmt_eve_date desc, gmt_eve_time desc

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 07:24:58

This is the whole recordset that was returned. As you can see the previous example isn't even there and I would expect the results to be about 900 there are only 39 here

801968852 J7719 VIE 20041007 20041011 2116
801973937 EB1151/GB/AT VIE 20041217 20041221 2241
801975005 J8202 STO 20050119 IN TRANSIT 20050119 2042
801976213 20050204
801976213 20050204
801976213 20050205
801976213 20050207
801976227 20050207
801976227 20050207
801976227 20050204
801976227 20050204
801976227 20050205
801976235 20050205
801976235 20050207
801976235 20050205
801976235 20050205
801976235 20050204
801976235 20050204
801976235 20050204
801976235 20050205
801976235 20050205
801976244 20050205
801976244 20050207
801976244 20050204
801976244 20050204
801976244 20050205
801976258 20050205
801976258 20050204
801976258 20050204
801976261 20050204
801976261 20050204
801976261 20050204
801976275 20050204
801976275 20050204
801976275 20050204
801976275 20050205
801976289 20050204
801976289 20050204
801976289 20050204

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:34:21

can you try this...

select con_num, cust_ref,dest_dep,con_date, summ_desc, max(gmt_eve_date), max(gmt_eve_time)
from delivery
group by con_num,cust_ref,dest_dep,con_date, summ_desc

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 07:40:01

Better but there are still multiples

747407167 NVM/GB/SWE2 MMA 20041119 DELIVERED 20041126 1130
747407167 NVM/GB/SWE2 MMA 20041119 EXCEPTION 20041122 1055
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 2349
747407175 J8135/10005 BO1 20041217 DELIVERED 20041221 1457
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 2016

As you can see its giving the three oldest dates rather than

747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 0715

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:47:56

i am missing something.

can you try this queries. and let me know the result

select d.con_num, cust_ref,dest_dep,con_date, summ_desc, gmt_eve_date, gmt_eve_time
from delivery d inner join
(select con_num,max(gmt_eve_date) ged,max(gmt_eve_time) gdt
from delivery
where sign = ''
group by con_num) p
on d.con_num=p.con_num and d.gmt_eve_date=p.ged and gmt_eve_time=gdt
where sign = ''
order by d.con_num,
gmt_eve_date desc, gmt_eve_time desc

and

select con_num,max(gmt_eve_date) ged,max(gmt_eve_time) gdt
from delivery
where sign = ''
group by con_num

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 07:51:35

The first one gave me 1000's of results but the second one gave me

747407167 20041126 2349
747407175 20041221 2016

Going back to your previous example but slightly modified this gets me close

select con_num, cust_ref,dest_dep,con_date, summ_desc, max(gmt_eve_date), max(gmt_eve_time)
from delivery
where sign = ''
group by con_num,cust_ref,dest_dep,con_date, summ_desc

747407167 NVM/GB/SWE2 MMA 20041119 EXCEPTION 20041122 1055
747407167 NVM/GB/SWE2 MMA 20041119 IN TRANSIT 20041126 2349
747407175 J8135/10005 BO1 20041217 IN TRANSIT 20041221 2016

Somewhere between the two and I think we may get it

Nazim
A custom title

1408 Posts

Posted - 2005-02-07 : 07:58:14

My Mind is full of Rust!!!.

do me a favour, go to Enterprise Manager , right click on the table , All Tasks->Generate Sql Script .

copy the same and paste it here.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

jparker
Posting Yak Master

118 Posts

Posted - 2005-02-07 : 08:02:47

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[delivery]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[delivery]
GO

CREATE TABLE [dbo].[delivery] (
[com_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_num] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cou_acc_num] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[org_dep] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_time] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[division] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pro_cod] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dest_dep] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_ref] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[alt_con_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dat_com] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_add2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_prov] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_post] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[send_cou] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_nam] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_add2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_prov] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_post] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_cou] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_nam] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_add2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_prov] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_post] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col_cou] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_nam] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[el_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_add2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_prov] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_post] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[del_cou] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[special] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[act_wei] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_wei] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wei_unit] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[che_wei] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[act_vol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_vol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vol_unit] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[che_vol] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[act_num] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[con_num_pie] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stat_code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loc_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loc_time] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stat_des] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lang] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sign] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dep_where] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[summ_desc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gmt_eve_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gmt_eve_time] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pie_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pie_seq_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_pie_add] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[time_pie_add] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_pie_ref] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cust_pie_seq_num] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pie_id1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pie_seq_id1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_pie_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[time_pie_add1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stat_code1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loc_date1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loc_eve1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stat_des1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lang1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sign1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dep_where1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[summ_desc1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gmt_eve_date1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gmt_eve_time1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources