How to grab the first hit of a record....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to grab the first hit of a record....

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-01-12 : 20:42:46

Hi,
I am trying to write a query that has to grab the first hit of the record.

My table is

Id........Datecolumn....Name.....Address..........City
1234......2005-01-24....Ron.....12,xyz street....Oklahoma
1234......2005-01-24....Ron......PO.BOX 123.......Chicago
4567......2005-12-30....King.....44,some street....Dallas

There are quite a few records like this that has same Id field but different address and city field. Is there a way, we can grab the first hit of the record with the same ID.I mean if the Id is coming more than once then grab the first record of that ID. If the ID is not coming more than once then grab the record as it is.

From above table: I am trying to get this output:

Id........Datecolumn....Name.....Address..........City
1234......2005-01-24....Ron.....12,xyz street....Oklahoma
4567......2005-12-30....King.....44,some street....Dallas

Will appreciate any advice on this.

Thanks

Raj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-12 : 22:14:59

Here is one way though I doubt it will be very efficient on a lot of rows.


create table #temp (id int, dt datetime, nm varchar(10), ad varchar(25), city varchar(10))
insert #temp
select 1234,'2005-01-24','Ron','12 xyz street', 'Oklahoma' union all
select 1234,'2005-01-24','Ron','POBOX 123', 'Chicago' union all
select 4567,'2005-12-30','King','44 some street', 'Dallas'

select	b.id
	,b.dt
	,b.nm
	,b.ad
	,b.city
from	(
	select id, min(checksum(convert(varchar,dt)+nm+ad+city)) cs
	from	#temp
	group by id
	) a
join	#temp b
		on b.id = a.id
		and checksum(convert(varchar,b.dt)+b.nm+b.ad+b.city) = a.cs

go
drop table #temp

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-12 : 22:34:40

I alway like the randomese of what is asked for. Do you have any rules as to what your really want?

There is no first. The order of data in a database has no meaning. What rules can you supply as to what data you expect?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 01:49:44

In TG's example, try this code also

Select * from #temp T
where city=(select top 1 city from #temp where id=T.id)

Madhivanan

Failing to plan is Planning to fail

rkumar28
Starting Member

49 Posts

Posted - 2006-01-13 : 04:09:28

Thanks for the prompt reply. All I am trying is to grab one record out of many if the ID for that record is appearing more than once and display all the fields.

I tried the fix but am still getting the duplicate IDs. I am getting exactly the
same number of records that are stored in the table.
I checkedthe result of the checksum in the query below and that field is the same for all the rows.

I tried the two suggstions below:

select b.TAXID
,b.DW_EFF_DT
,b.DW_FIRST_NM
,b.DW_LAST_NM
,b.DW_ADDR_1
,b.DW_ADDR_2
,b.DW_ADDR_3
,b.DW_ADDR_4
,b.DW_CITY_NM
,b.DW_ST_CD
,b.DW_ZIP_CD
from (
select TAXID,
min(checksum(convert(varchar,DW_EFF_DT)
+DW_FIRST_NM+DW_LAST_NM+DW_ADDR_1+DW_ADDR_2+DW_ADDR_3+DW_ADDR_4+DW_CITY_NM+DW_ST_CD+DW_ZIP_CD)) cs
from sap
group by TAXID
) a
join sap b on b.TAXID = a.TAXID
and
checksum(convert(varchar,b.DW_EFF_DT)
+b.DW_FIRST_NM+b.DW_LAST_NM+b.DW_ADDR_1+b.DW_ADDR_2+b.DW_ADDR_3+b.DW_ADDR_4+b.DW_CITY_NM+b.DW_ST_CD+b.DW_ZIP_CD) = a.cs

Also, I tried the suggestion below:

Select distinct * from sap1099
where
(
DW_CITY_NM =(select top 1 DW_CITY_NM from RKumar.sap1099 where TAXID = T.TAXID
)

This works fine for the scenario where city is not the same. But I am still getting the duplicate ID where the city name is same but the other fields are different.

Thanks

Raj

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-13 : 04:33:28

Since you don't seem to have any strict rules for defining which is the first record, you could -

SELECT distinct ID into #listofids FROM yourtable
go
alter table #listofids ADD Datecolumn datetime, [Name] varchar(50), Address varchar(50), City varchar(50)
go
UPDATE #listofids SET lof.datecolumn = yourtable.datecolumn, lof.[name] = yourtable.[name], lof.address = yourtable.address, lof.city = yourtable.city
FROM #listofids lof INNER JOIN yourtable yt ON lof.ID = yt.ID

SELECT * from #listofids

The syntax above may need sanity checking.

-------
Moo. :)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-13 : 05:06:59

SQL Server 2005. Obviously, the row you get for each Id will be arbitrary, and may vary between executions.


SELECT Id, Datecolumn, Name, Address, City
FROM (
    SELECT Id, Datecolumn, Name, Address, City,
      ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id) AS rn
    FROM YourTable
  ) AS A
WHERE rn = 1

[edit]Oops, forgot that ORDER BY is non-optional on ranking functions, even though it doesn't have to result in a deterministic ranking![/edit]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 07:32:41

>>am still getting the duplicate IDs
can you post a few "insert statements" for duplicate rows that are being returned?

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-01-19 : 13:35:47

I'm not sure if this will work for your particular situation, but I have used soemthing similar in the past to get the "first" (by first I mena random) items of a table.


create table #temp (id int, dt datetime, nm varchar(10), ad varchar(25), city varchar(10))
insert #temp
select 1234,'2005-01-24','Ron','12 xyz street', 'Oklahoma' union all
select 1234,'2005-01-24','Ron','POBOX 123', 'Chicago' union all
select 4567,'2005-12-30','King','44 some street', 'Dallas'

SELECT
    t.ID,
    t.dt, 
    t.nm, 
    t.ad, 
    t.city 
FROM
(
    SELECT BINARY_CHECKSUM(*) AS Rank, *
    FROM #temp
    GROUP BY ID ,dt, nm, ad, city 
) t
INNER JOIN
(
    SELECT MIN(BINARY_CHECKSUM(*)) AS Rank, id
    FROM #temp
    GROUP BY ID
) t2
ON t.Rank = t2.Rank


DROP TABLE #temp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-01-19 : 13:58:43

A quick and dirty way to ensure that you get every ID is to use a temp table and set the id column as an index with the IGNORE_DUP_KEY attribute.


create table #temp (id int, dt datetime, nm varchar(10), ad varchar(25), city varchar(10))
insert #temp
select 1234,'2005-01-24','Ron','12 xyz street', 'Oklahoma' union all
select 1234,'2005-01-24','Ron','POBOX 123', 'Chicago' union all
select 4567,'2005-12-30','King','44 some street', 'Dallas'

-- Create a temp table with the same schema
SELECT * INTO #temp2
FROM #temp
WHERE 1 = 2

CREATE UNIQUE INDEX ix_foo ON #temp2(id) WITH IGNORE_DUP_KEY

INSERT INTO #temp2
SELECT *
FROM #temp

SELECT * FROM #temp2


DROP TABLE #temp
DROP TABLE #temp2

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-19 : 14:01:00

What is the primary key of this table? Obviously, it is not the "ID" column. Without that information, it is impossible to provide a solution, or, in fact, write *any* SQL that works reliably with your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources