Solved

Unique records

Posted on 2009-05-06
6
236 Views
Last Modified: 2012-05-06
I have a table that has the following sample information:

ID_NUM                                                   VER               PN
-------------------------------------               ----                ------------------
0000000000000000001000016      001      2T-MNILA-AA
0000000000000000001000019      001      338602-121
0000000000000000001000019      001      338602-161
0000000000000000001000019      001      338602-291
0000000000000000001000019      001      338602-A21
0000000000000000001000019      001      338602-AA1
0000000000000000001000019      001      338602-AB1
0000000000000000001000019      001      338602-001
0000000000000000001000019      001      338602-371
0000000000000000001000019      001      338602-391
0000000000000000001000019      001      338602-AD1
0000000000000000001000020      001      36-60382-25
0000000000000000001000023      001      DY-1280A-AD
0000000000000000001000024      001      DS-SWRCA-AA
0000000000000000001000025      001      CT-WNTER-20
0000000000000000001000031      001      36-60382-25
0000000000000000001000032      001      CT-NOVUS-BC
0000000000000000001000032      001      CT-NOVUS-CC
0000000000000000001000032      001      CT-NOVUS-DC
0000000000000000001000033      001      3X-75CAA-AA
0000000000000000001000034      001      70-40530-03
0000000000000000001000035      001      263661-002
0000000000000000001000035      001      339100-001

I need to create a function or sql statement that can identify the first record when there are multiple records in the table.  If I use the group-by function, it doesn't find the unique ones.
0
Comment
Question by:Fairfield
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24318767
well, I don't know how you designate "first", but here is a way to do it:

select * from (
select *, ranking = dense_rank() over(partition by id_num order, ver order by pn, newid())
from tablename
) a
where ranking = 1
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24318772
do you mean

with a as (Select *, row_number() over(partition by id_num order by ver) as row
from your_table)

select * from a where rown=1
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24318777
What won't this show you?

select id_num,ver,min(pn)
from SomeTable
group by id_num,ver
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24318884
hmmm....while Brandons solution does work, so do the others....
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24318930
While they will all function.  I personally chose my method due the fact that row_number() would not have to assign a value to every record then filter.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24318960
true...but its almost exactly the same way min() works.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question