Simple query yet difficult

Question

Post reply

Simple query yet difficult

DBABC

SSCarpal Tunnel

Points: 4813
More actions
May 14, 2009 at 10:09 am

#221196

So here is the data
Id Term
1, A
2, A
3, A
3, B
i need a query that can give me following result
1, A
2, A
3, A
Means i need only 1 record for each Id (it could be either one of the multiple records)

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Look for ROW_NUMBER() function with PARTITION BY clause in Books Online.

--Ramesh

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

DECLARE @MyTable TABLE

(Id INT,

Team CHAR(1))

INSERT INTO @MyTable

SELECT 1, 'A' UNION ALL

SELECT 2, 'A' UNION ALL

SELECT 3, 'A' UNION ALL

SELECT 3, 'B'

SELECT

Id,MAX(Team)

FROM @MyTable

GROUP BY Id

Look at how the Group by works in BOL and the aggregation functions that go with it, e.g. MAX,MIN etc

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Rob Goddard SSCrazy Points: 2622 More actions · Answer 3

In that example grouping by ID and using MIN or MAX on the other column will do it.

If there are a lot of cols and you need them all to relate to the same record, see the suggestion above. (Ramesh's) 😉

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 4

Ramesh (5/14/2009)
Look for ROW_NUMBER() function with PARTITION BY clause in Books Online.

Thanks for replying Ramesh, i know ROW_NUMBER() function, but can you please explain how that can be used to get the required result? Becuase ROW_NUMBER will generate a unique id for the data but will it give me the data i want?

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 5

Christopher Stobbs (5/14/2009)
DECLARE @MyTable TABLE (Id INT, Team CHAR(1)) INSERT INTO @MyTable SELECT 1, 'A' UNION ALL SELECT 2, 'A' UNION ALL SELECT 3, 'A' UNION ALL SELECT 3, 'B' SELECT Id,MAX(Team) FROM @MyTable GROUP BY Id

Thanks for this query, but the problem is in actual table i have tons of columns so i cannot use MIN,MAX for each of them

(for simplicity i gave a temporary table in my orignal post)

Rob Goddard SSCrazy Points: 2622 More actions · Answer 6

usman.tanveer (5/14/2009)
Thanks for this query, but the problem is in actual table i have tons of columns so i cannot use MIN,MAX for each of them
(for simplicity i gave a temporary table in my orignal post)

Is there a unique key on the table?

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 7

Rob Goddard (5/14/2009)
In that example grouping by ID and using MIN or MAX on the other column will do it.
If there are a lot of cols and you need them all to relate to the same record, see the suggestion above. (Ramesh's) 😉

Thanks, yes i have a lot of cols, but still i am not sure how Row_number will work for me

here is the query with Row number

DECLARE @MyTable TABLE

(Id INT,

Team CHAR(1))

INSERT INTO @MyTable

SELECT 1, 'A' UNION ALL

SELECT 2, 'A' UNION ALL

SELECT 3, 'A' UNION ALL

SELECT 3, 'B'

SELECT

Id,Team, ROW_NUMBER() OVER (ORDER BY Id) RowNum

FROM @MyTable

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

If you could have read Books Online, you would surely be able to solve it on your own. Anyways, here is the solution for the sample data provided by Christopher (thanks for this, Chris:-))

DECLARE @MyTable TABLE

(Id INT,

Team CHAR(1))

INSERT INTO @MyTable

SELECT 1, 'A' UNION ALL

SELECT 2, 'A' UNION ALL

SELECT 3, 'A' UNION ALL

SELECT 3, 'B'

; WITH Test

AS

(

SELECTROW_NUMBER() OVER( PARTITION BY Id ORDER BY Team ) AS RowNumber, *

FROM@MyTable

)

SELECTId, Team

FROMTest

WHERERowNumber = 1

--Ramesh

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 9

DBABC

SSCarpal Tunnel

Points: 4813

May 14, 2009 at 11:03 am

#994723

Thanks everyone, specially Ramesh.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

usman.tanveer (5/14/2009)
Christopher Stobbs (5/14/2009)
DECLARE @MyTable TABLE (Id INT, Team CHAR(1)) INSERT INTO @MyTable SELECT 1, 'A' UNION ALL SELECT 2, 'A' UNION ALL SELECT 3, 'A' UNION ALL SELECT 3, 'B' SELECT Id,MAX(Team) FROM @MyTable GROUP BY Id
Thanks for this query, but the problem is in actual table i have tons of columns so i cannot use MIN,MAX for each of them
(for simplicity i gave a temporary table in my orignal post)

Sure you can... how many columns?

--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)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 11

usman.tanveer (5/14/2009)
Thanks everyone, specially Ramesh.

You are welcome, and for all your future posts read this article on how to post questions to get better answers[/url]

--Ramesh