x

Greatest Entry for a member

In my query each CLC_ID can have multiple CLC_Contact_SEQ as every new entry creates a new higher number for that member. I need to bring back the greatest entry for each ID number. Right now I just have them ordered so each ID I can see all of the entries. I am new to SQL and any help would be GREAT!

Select CLC_CLNT_NUM,
 CLC_CONTACT_TYPE,
 CLC_CONTACT_SEQ,
 CLC_CONTACT_DATa,
 CLC_ID
FROM dbo.CLIENT_CONTACT_INFO 
WHERE cLC_CONTACT_TYPE = 'INTR'
ORDER by CLC_CLNT_NUM
more ▼

asked Aug 28, 2012 at 09:15 PM in Default

furyplym gravatar image

furyplym
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This is untested and off the top of my head, but if you just want to get the greatest CLC_Contact_Seq then you can use max like:

select 
  CLC_ID, 
  max(CLC_Contact_Seq) as CLC_contact_seq
from 
  dbo.CLIENT_CONTACT_INFO 
group by 
  CLC_ID

If you want to bring back the other columns, but only for the row that has the max contact sequence, then one approach is to do an inner join to the results of the above query. That would look something like:

Select 
 i1.CLC_CLNT_NUM, 
 i1.CLC_CONTACT_TYPE, 
 i1.CLC_CONTACT_SEQ, 
 i1.CLC_CONTACT_DATa, 
 i1.CLC_ID
From 
 dbo.CLIENT_CONTACT_INFO i1
 inner join
 (select CLC_ID, max(CLC_Contact_Seq) as CLC_contact_seq
 from dbo.CLIENT_CONTACT_INFO
 group by clc_id) i2
 on 
  i1.clc_id = i2.clc_id and i1.clc_contact_seq = i2.clc_contact 
where 
 i1.cLC_CONTACT_TYPE = 'INTR' 
order by 
 i1.CLC_CLNT_NUM
That would certainly not be the only option, but it is one of the conceptually simpler ones. Essentially, it is filtering the results of the table by the rows that have the greatest contact sequence for that ID number.
more ▼

answered Aug 28, 2012 at 10:30 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Another approach would be to use the ROW_NUMBER function:

SELECT CLC_CLNT_NUM, CLC_CONTACT_TYPE, CLC_CONTACT_SEQ, CLC_CONTACT_DATA, CLC_ID,
    ROW_NUMBER() OVER (PARTITION BY CLC_CLNT_NUM ORDER BY CLC_CONTACT_SEQ DESC) AS RowNum
FROM dbo.CLIENT_CONTACT_INFO
WHERE RowNum = 1
more ▼

answered Aug 29, 2012 at 08:44 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

Definitely a great technique, though I think its a lot harder to understand that concept for someone new to sql.
Sep 04, 2012 at 04:32 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Thank you so much for all of the suggestions. Between them I was able to get my query to do what I needed. Thanks again for helping me!
more ▼

answered Sep 06, 2012 at 06:39 PM

furyplym gravatar image

furyplym
10 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x369

asked: Aug 28, 2012 at 09:15 PM

Seen: 525 times

Last Updated: Sep 06, 2012 at 06:39 PM