question

furyplym avatar image
furyplym asked

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
query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Another approach would be to use the [ROW_NUMBER][1] 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 [1]: http://msdn.microsoft.com/en-us/library/ms186734.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Definitely a great technique, though I think its a lot harder to understand that concept for someone new to sql.
0 Likes 0 ·
furyplym avatar image
furyplym answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.