Return first record of each key

Question

Post reply

Return first record of each key

Rich96

SSC Eights!

Points: 947
More actions
October 1, 2008 at 11:24 am

#199193

I have a table that I want to extract the first record of each KEY. I'm using the following select but it's not returning the desired set. What am I missing????
SELECT KEY, COL1, COL2
FROM TABLE1
GROUP BY KEY, COL1, COL2
HAVING COUNT(*) < 2
Current Result set:
KEY COL1 COL2
10203003275D/C SET 0138 12/31/02 00086116AMAZON.COM
10203003275800-201-7575 WA
10203003291D/C SET 0139 12/27/02 27300801SALTGRASS
10203003291DALLAS TX
10203008819D/C SET 0138 12/31/02 00086116AMAZON.COM
10203008819800-201-7575 WA
Desired Result set:
KEY COL1 COL2
10203003275D/C SET 0138 12/31/02 00086116AMAZON.COM
10203003291D/C SET 0139 12/27/02 27300801SALTGRASS
10203008819D/C SET 0138 12/31/02 00086116AMAZON.COM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

If you are on SQL Server 2005, you can use the ROW_NUMBER() function to achieve this.

for example

DECLARE @t TABLE ( VARCHAR(30), COL1 VARCHAR(30), COL2 VARCHAR(20))

INSERT INTO @t(, col1, col2) SELECT '10203003275','D/C SET 0138 12/31/02 00086116','AMAZON.COM '

INSERT INTO @t(, col1, col2) SELECT '10203003275','800-201-7575','WA'

INSERT INTO @t(, col1, col2) SELECT '10203003291','D/C SET 0139 12/27/02 27300801','SALTGRASS'

INSERT INTO @t(, col1, col2) SELECT '10203003291','DALLAS','TX'

INSERT INTO @t(, col1, col2) SELECT '10203008819','D/C SET 0138 12/31/02 00086116','AMAZON.COM'

INSERT INTO @t(, col1, col2) SELECT '10203008819','800-201-7575','WA'

;WITH cte AS (

SELECT *, ROW_NUMBER() OVER(PARTITION BY ORDER BY [col1]) AS seq

FROM @t

)

SELECT , col1, col2

FROM cte

WHERE seq = 1

/*

key col1 col2

------------------------------ ------------------------------ --------------------

10203003275 800-201-7575 WA

10203003291 D/C SET 0139 12/27/02 27300801 SALTGRASS

10203008819 800-201-7575 WA

*/

.

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Hi,

there are 2 main problems in what you were trying. First, COUNT(*) < 2 means that there are no duplicates (given the used GROUP BY)... not that it is the first row with such values.

Second, there isn't anything like "first row", unless you use explicit ORDER BY - meaning that without ORDER BY, you have no guaranty that the first returned row will always be the same. I suspect, that your first row definition is based on order of rows in some text file, maybe you are importing the rows from it? If yes, then it would be good to mark your rows with numbers during the import, so that you have something to find the row you need - either with ORDER BY, or as a condition in WHERE clause.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

SELECTCol1,

Col2,

Col3

FROMTable1

WHERECol3 > ''

SELECTCol1,

Col2,

Col3

FROMTable1

WHERECol2 LIKE 'D/C SET%'

Maybe if you post sample data like previous post we can help you more.

N 56°04'39.16"
E 12°55'05.25"

Rich96 SSC Eights! Points: 947 More actions · Answer 4

Thanks for the replies!

Jacob's solution was right on. The only thing I tweaked was the Order By, I went with the KEY instead of the column.

with cte as (

select *, row_number() over(partition by efhkey order by efhkey) as seq

from Q2eftdescriptions

)

select *

from cte

where seq = 1

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Will that work when records in table are stored in other order?

old

10203003275 D/C SET 0138 12/31/02 00086116 AMAZON.COM

10203003275 800-201-7575 WA

new

10203003275 800-201-7575 WA

10203003275 D/C SET 0138 12/31/02 00086116 AMAZON.COM

N 56°04'39.16"
E 12°55'05.25"

Rich96 SSC Eights! Points: 947 More actions · Answer 6

Fortunately the records will always be in sequential order based on the key column. I verified with the programmer that same question you had and it will never be:

new

10203003275 800-201-7575 WA

10203003275 D/C SET 0138 12/31/02 00086116 AMAZON.COM

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Rich96 (10/2/2008)
Fortunately the records will always be in sequential order based on the key column. I verified with the programmer that same question you had and it will never be

"Never" is a very dangerous word in this context. It may mean "as long as I'm working for this company", or "until some future release of our software", or something similar. Be cautious before you accept any statements of that type 🙂

However, you say that you use the key for ordering... how can you order by the key, if the key has the same value in both rows? Again, I have to remind you that there is no guaranty that the rows will be returned in the same order without ORDER BY (and order by some column, that will be different for every row with the same key!). Otherwise rows can be returned 99% of the time in the same order, but then suddenly they will be returned in a different one, and where will you be?

Rich96 SSC Eights! Points: 947 More actions · Answer 8

So true Vladen. I'm having to forge ahead with the ASSumption that it won't change.....

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 9

Rich96 (10/2/2008)
So true Vladen. I'm having to forge ahead with the ASSumption that it won't change.....

You really do not have to (and should not) go with that assumption.

You should really try to come up with a solution that will work when the rows are not sorted as you expect them to be.

If you post some sample data, we can try to help. 🙂

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

According to sample data, this is what you need.

SELECT Col1,

Col2,

Col3

FROM Table1

WHERE Col2 LIKE 'D/C SET%'

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Rich96 (10/2/2008)
Fortunately the records will always be in sequential order based on the key column. I verified with the programmer that same question you had and it will never be:
new
10203003275 800-201-7575 WA
10203003275 D/C SET 0138 12/31/02 00086116 AMAZON.COM

Oh, be careful... Without some other column present to sort on, both you and the "programmer" are very wrong. There is no guarantee of the order of rows returned unless you use an ORDER BY. I learned the hard way that even a clustered index does not guarantee it for anything except an UPDATE that also uses an Index Scan on the clustered index... it is not reliable for selects.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 12

Hi Jeff,

I'm glad you posted this, because I was trying to explain it several times, but it seems that Rich didn't understand (or understood differently than I meant it).

Let's hope he will realize now what the situation is... lack of ordering can have very unpleasant consequences, and it is hard to fix later, because the problem can occur irregularly - just sometimes.

jacob sebastian SSChampion Points: 11822 More actions · Answer 13

I agree with both of you. I had a tough time explaining the same at the UG meeting.

.