Tricky Query

Question

Post reply

Tricky Query

DBABC

SSCarpal Tunnel

Points: 4813
More actions
October 9, 2008 at 8:02 am

#194469

Hi,
Suppose i have the following data set.
C1 C2 C3 C4
a@a.com 1-Jan-08 AAA BBB
a@a.com 1-Jan-08 CCC DDD
b@b.com 1-Jan-08 123 456
b@b.com 1-Jan-08 AAA BBB
Each email address and date combination has multiple values in C3, C4 columns.
I want a query that returns only one row for each email and date column (it could be first row or last row). i.e i want following result set.
C1 C2 C3 C4
a@a.com 1-Jan-08 AAA BBB
b@b.com 1-Jan-08 123 456
OR IT COULD BE
C1 C2 C3 C4
a@a.com 1-Jan-08 CCC DDD
b@b.com 1-Jan-08 AAA BBB
I dont have any primary key on this table.
Any ideas?
THanks,
Usman

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

WITH CTE AS(

SELECT C1,C2,C3,C4,

ROW_NUMBER() OVER(PARTITION BY C1,C2 ORDER BY C3,C4) AS rn

FROM mytable)

SELECT C1,C2,C3,C4

FROM CTE

WHERE rn=1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

If it doesn't matter what values from Columns 3 and 4 are returned, why return them at all?

Why not just select c1,c2 from mytable Group BY C1, C2 ?

To help us help you read this[/url]For better help with performance problems please read this[/url]

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 3

Because i want atleast one value for c3 and c4, and it could be either the oldest value user inserted or the most recent value user inserted.

Thanks Mark, i m trying your query.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

DROP TABLE #Sample

CREATE TABLE #Sample (C1 VARCHAR(20), C2 VARCHAR(20), C3 VARCHAR(20), C4 VARCHAR(20))

INSERT INTO #Sample (C1, C2, C3, C4)

SELECT 'a@a.com', '1-Jan-08', 'AAA', 'BBB' UNION ALL

SELECT 'a@a.com', '1-Jan-08', 'CCC', 'DDD' UNION ALL

SELECT 'b@b.com', '1-Jan-08', '123', '456' UNION ALL

SELECT 'b@b.com', '1-Jan-08', 'AAA', 'BBB'

SELECT C1, C2, MAX(C3), MAX(C4)

FROM #Sample

GROUP BY C1, C2

or

SELECT C1, C2, MIN(C3), MIN(C4)

FROM #Sample

GROUP BY C1, C2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden