Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select distinct question 1

Status
Not open for further replies.
williamkremer

williamkremer

Programmer
Oct 27, 2005
61
Hi all -- I can't remember how to do this: I want to select distinct company, field2, datefield, field4 where field4 = 'Val'. Now, I only want one listing for the company, and that is the record with the most recent datefield. Make sense? Thanks a bunch

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Sort by date Sort by votes
This should do it
Code: 
select  t1.company, t1.field2, t1.datefield, t1.field4 
from table1 t1 join (select Max(datefield) as Maxdate,company
from table1 group by company) z on t1.datefield = z.Maxdate
and t1.company = z.company
where t1.field4 = 'Val'

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Code: 
select A.company, A.field2, A.datefield, A.field4
from blah A
inner join
(	select company, max(datefield) as maxdatefield
	from blah
	group by company
) B
on A.company=B.company and A.datefield=B.maxdatefield
where A.field4 = 'Val'

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
select c1.company, c1.Key1, c1.Key2, c1.Key3,
c1.Key4, c1.key5, ch.rectype, ch.ondate from contact1 c1 join (select max(ch.ondate)
as LastCall, company from contact1 group by company) z join conthist ch
on (c1.ondate = z.Lastcall)
and (c1.company = z.company)
and (c1.accountno = ch.accountno)
where ch.rectype like '%CO%'

Almost got it -- but an error about the "where"

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
Code: 
select c1.company, c1.Key1, c1.Key2, c1.Key3,
c1.Key4, c1.key5, ch.rectype, ch.ondate 
from contact1 c1 
join (select max(ch.ondate)
as LastCall, company from contact1 group by company) z 
on  c1.ondate = z.Lastcall
join  conthist ch on c1.accountno = ch.accountno
and c1.company = z.company
where ch.rectype like '%CO%'

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
The column prefix 'ch' does not match with a table name or alias name used in the query.

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
Waitamicrot... inner query does GROUP BY company... and join is on company and max(date) AND accountno. Is that OK?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why
[banghead]
 
Upvote 0 Downvote
No, it is in conthist. I've been trying all sorts of variations, and nothing has worked so far!!! Yikes!

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
then this is incorrect
Code: 
select c1.company, c1.Key1, c1.Key2, c1.Key3,
c1.Key4, c1.key5, ch.rectype, [COLOR=red]ch.ondate[/color] 
from contact1 c1 
join (select max(ch.ondate)
as LastCall, company from contact1 group by company) z 
on  [COLOR=red]c1.ondate[/color] = z.Lastcall
join  conthist ch on c1.accountno = ch.accountno
and c1.company = z.company
where ch.rectype like '%CO%'
they have to be both ch or c1

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
I'm not sure what you mean, Denis. The company name, and the Key fields are in C1, and the ondate and rectype fields are in conthist. So I want to list the last time each company was called (company name distinct with max(ondate))and the values in the key fields on that date along with the rectype value on that date.

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
In this part:

join (select max(ondate)as LastCall,
company from contact1 group by company)

Ondate is in conthist, and company is in contact1. Shouldn't there be a join or something in there? and if so, what about elsewhere in the query? Join the tables twice? Now I'm getting dizzy. I figured that this would be easy, but somehow it has gotten to be, um, confusing? thanks for the help so far. There must be a way......

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
Contact1:
CREATE TABLE [dbo].[CONTACT1] (
[ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPARTMENT] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SECR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PHONE2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEAR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MERGECODES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEON] [datetime] NULL ,
[CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OWNER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTDATE] [datetime] NULL ,
[LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U_COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U_KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Conthist:
CREATE TABLE [dbo].[CONTHIST] (
[USERID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SRECTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RECTYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ONDATE] [datetime] NULL ,
[ONTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTVCODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RESULTCODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DURATION] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REF] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINKRECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOPRECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEON] [datetime] NULL ,
[CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTDATE] [datetime] NULL ,
[LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
let's see

Code: 
select c1.company, c1.Key1, c1.Key2, c1.Key3,
c1.Key4, c1.key5, ch.rectype, c1.ondate 
from contact1 c1 
join  conthist ch on c1.accountno = ch.accountno
join (select max(ondate)
as LastCall, accountno from contact1 group by accountno) z 
on  c1.ondate = z.Lastcall
and c1.accountno = z.accountno
where ch.rectype like '%CO%'

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ondate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ondate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ondate'.

Ummmmm.

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
hehe ask for DDL and then still mess it up [flush] (meaning me)

this should work (famous last words)

Code: 
select c1.company, c1.Key1, c1.Key2, c1.Key3,
c1.Key4, c1.key5, ch.rectype, ch.ondate 
from contact1 c1 
join  conthist ch on c1.accountno = ch.accountno
join (select max(ondate)
as LastCall, accountno from conthist group by accountno) z 
on  ch.ondate = z.Lastcall
and c1.accountno = z.accountno
where ch.rectype like '%CO%'

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
You got it Denis. You get a star for your dedication. AND For the very, very last query!!!! I'm studying your query now. Thanks

"You cannot hold a torch to another man's path without brightening your own"
Best regards,
Bill
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
SSMS question 1
Replies
4
Views
1K
Sana Shazadi
S
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
900
SashaBuilder3
SashaBuilder3
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
874
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
671
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
595
grnzbra
grnzbra

Part and Inventory Search

Sponsor

Back
Top