If two different values are present for a dup id, use one, else use the other 1

[larrydavid]

Hello,

I have a resultset which is returning records that may have two on one MemberID:

Current Output:
[MemberID]	[Category]		[Type]
12345		ABC			test
12345		XYZ			test
12777		ABC			test
12888		FGD			test

Desired Output:
[MemberID]	[Category]		[Type]
12345		ABC			test
12777		ABC			test
12888		FGD			test

Query:
SELECT   m.MemberID, 
	 vw.Category,
	 vw.Type,	
FROM	 dbo.TestVW vw JOIN
	 dbo.TestMember m ON vw.MemberKey = m.MemberKey		
WHERE	 vw.Type = 'test' 
GROUP BY m.MemberID,
	 vw.Category,
	 vw.Type

Any help would be greatly appreciated.

Thanks and Regards,
Larry

 

[Olaf Doschke]

If you group by category, different categories appear in the result. So first thing for sure is, you remove that field from the group by. You also don't need group by type, as you only have one type by the where clause.

In regard of getting one cetegory your option are aggregate function. Look that up in books online. Several aggregate functions only work for numeric columns/expressions, your options mainly are MIN or MAX. FIRST is what you'd want, but that aggregate function doesn't exist, actualla Access/Jet offers some non standard aggregate function here, which T-SQL doesn't. If it would be important you'd need a solution of the often asked about question "top N of each group". Google "t-sql top 1 of each group" and you get tons of results, I spare the time to write yet another explanation.

If you don't care which, simply make it

[pre]SELECT m.MemberID,
MIN(vw.Category) as Cetegory,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID[/pre]

Just know this will not take the first category, but the min value, the aphabetic first category.
Besides the first category in what order? Something can be first chronological, alphabetical, most orders on that category and many more ways to sort data. Data isn't stored in any order, also no default order, not even chronological. Always remember sql processes sets.

Bye, Olaf.

 

[larrydavid]

Hi Olaf,

Thanks for tying to help but this left me more confused then when I asked the question. I was able to get a better guide toward a solution at another forum.

Cheers!

Larry

 

[Olaf Doschke]

The basic definition of GROUP BY is: Your result can have fields you groupd by only, everything else in the field list needs to be aggregates. If you don't know what aggregate functions are, you missed the most important part of grouping data. If you always solved this so far by extending the group by clause with all the fields you need in the output you missed that this undermines the group definion you actually need.

If you don't understand this, you're not really ready for using GROUP BY in queries.

Bye, Olaf.

 

[larrydavid]

Hi Olaf,

ACtually I understand how to use the group by clause but in this case I was a bit confused by the implementation, which I have on a subsequent post now. I'm getting close using an inner/outer query using group by and tried a few things using row number(), partition functions. But, it seems I actually needed to be able to group on two dates instead of the member_id so my requirement was not correct to begin with. Now when you see the query I clearly group by these dates and just need to return a distinct row with a certain event category for a member with the same dates. Perhaps you can check my use of the group by and tell me what else I need to do to finish the query to return the desired output as shown on this example. (see post: "Return distinct record with distinct value if duplicate date range".

I am grateful for any help as usual.

Thanks,
Larry