Retrive UNIQUE Records

[talenx]

Hello all,
I’m currently having an issue with retrieving a unique record from a table that contains "almost duplicates" I mean the table contains valid data but each record is assigned a [Key] which is unique. Here is the table layout (I apologize for the length of the post.)

HSKey --unique key
HSCPMN, --company
HSDIVN, --division
HSVNDN, --vendor
HSPONB, --PO number
HSRVRN, --ref number
HSITMN, --item nbr
HSLNHS, --line nbr
The above fields combined make a unique record without the HSKey)
...
As well as some metric values (not important)

Ok here the thing I need to pull the FIRST UNIQUE record from the table without removing the HSKey field (I need it to refer to another table)

The data result look like this:
HSKey|HSCPMN|HSDIVN|HSVNDN|HSPONB|HSRVRN|HSITMN|HSLNHS
1--------1---------2---------90901----23468----87653----345323----1
2--------1---------4---------33456----99999----12345----324688----1
3--------1---------4---------33456----99999----12345----987688----2
4--------1---------4---------33456----99999----12345----987688----3
5--------1---------3---------23459----65432----34556----757644----1

You can have a record (PO) from a vendor for multiple items; the only thing that makes it unique is the "HSLNHS" the Line number the item was received. Unfortunately I can't use the "line number" field as a WHERE option because the record that contains the needed data it doesn't always have a line number of 1 applied to it ... though it is the FIRST record of the unique record set. Example

HSKey|HSCPMN|HSDIVN|HSVNDN|HSPONB|HSRVRN|HSITMN|HSLNHS
2--------1---------4---------33456----99999----12345----324688----1
3--------1---------4---------33456----99999----12345----987688----2
4--------1---------4---------33456----99999----12345----987688----3

If anybody has any suggestions please let me know...
Thanks
talenx

 

[ajc123]

Have you tried the TOP function? As in:

SELECT TOP 1
refid,
col1,
col2
FROM FOO
ORDER BY REFID

It's a benefit of SQL Server.

 

[swampBoogie]

There is no concept of first in a RDBMS.

Maybe you could use

select * from t q
 where HSLNHS in 
   (select min(HSLNHS)
      from t 
     where HSCPMN = q.HSCPMN
       and HSDIVN = q.HSDIVN
       and HSVNDN = q.HSVNDN 
       and HSPONB = q.HSPONB 
       and HSRVRN = q.HSRVRN 
       and HSITMN = q.HSITMN)

 

[ajc123]

SQL Server has "TOP n" clause as a feature of the SELECT statement and Oracle has the "ROWNUM" pseudocolumn, which provide shortcuts to a solution to the challenge.

Those shortcuts will provide you with working code (and this is a MS SQL Server forum). However, academic vanilla SQL, portable to any DB would require a solution as described by SwampBoogie above.

 

[talenx]

Thanks for the quick response. If I attempt the TOP option it of coarse does what it's suppose to by returning the TOP 1 value of select field in the record set, but this only returns one row out of thousands.
I need it to return a unique set of records made up of
HSCPMN|HSDIVN|HSVNDN|HSPONB|HSRVRN|HSITMN|HSLNHS
I can do this by using a SELECT TOP 100 PERCENT with a MAX(HSRVRN) this gives me the set of records that are unique but the problem is referring that record back to the original HS_KEY id field.

The current SQL for that looks like this:

SELECT TOP 100 PERCENT
HSCMPN,
HSDIVN,
HSDPTN,
HSWHSN,
HSVNDN,
HSPONB,
MAX(HSRVNR)as HSRVNR,
HSITMN,
HSLNHS

Into dbo.Staging_tPODetailDISTINCTTMP

FROM dbo.Staging_tPODetail
GROUP BY HSCMPN, HSDIVN, HSDPTN, HSWHSN, HSVNDN, HSPONB, HSITMN, HSLNHS

i have tried a SELECT with a inner join but it returns all records that were in the orinagl table that i pulled the unique record set from.

It looks like this
Select

i.HS_KEY,
a.HSCMPN,
a.HSDIVN,
a.HSDPTN,
a.HSWHSN,
a.HSVNDN,
a.HSPONB,
a.HSRVNR,
a.HSITMN,
a.HSLNHS

From dbo.Staging_tPODetailDISTINCTTMP a

Inner join dbo.Staging_tPODetail i
On (i.HSCMPN = a.HSCMPN) and
(i.HSDIVN = a.HSDIVN) and
(i.HSDPTN = a.HSDPTN) and
(i.HSWHSN = a.HSWHSN) and
(i.HSVNDN = a.HSVNDN) and
(i.HSPONB = a.HSPONB) and
(i.HSRVNR = a.HSRVNR) and
(i.HSITMN = a.HSITMN) and
(i.HSLNHS = a.HSLNHS)

The source (tPODetail) table has a record count ='589335'
The modified table that contain unique without an ID record count = '365211'
This result returns on a inner, left or join
Record count = '589135' maybe some nulls but the censuses is that the statements are returning all of the wrong tables record set count.

Thanks
talenx